March 20, 2016 at 6:08 pm
How to say it?
I have a fact table and within it a hierarchy, which I created. Now I need to write a DAX calculation that will SUM(facts) differently at one level of the hierarchy than another.
Example:
Heirarchy:
1. Engagement
2. Project type
3. Delivery type
4. Project Name
5. Cost Type
6. Resource
I need to create a single measure called Planned Rev, but the logic needed for its' value is going to be different depending on the level of the hierarchy at which it is being calculated.
Need to explicitly reference the different levels of the hierarchy when writing a DAX expression so that a unique set of instructions are used at each level of hierarchy.
Once surfaced in a pivot table , the appropriate section of the DAX calculation needs to be used at the intersection of each level of hierarchy and the measure.
eg. Measure is called 'Planned Rev'.
Psuedocode for 'Planned Rev' measure:
At Delivery type level, if Cost Type = A then SUM(TotalSubconPlannedRevenue) if Cost Type = B then (SUM(TotalFTEPlannedRevenue) else 0, at Project Name level, SUM(TotalFixedFeePlannedRevenue). At Cost Type level, set to 'NA'.
Goal being that Delivery type, Cost Type, and Project Name will be rows, and the measure column will render the appropriate calculations at the respective level of hierarchy.
Is this possible? Is this called Scoping? Can someone provide links? Can someone provide guidance of any kind?
--Quote me
March 21, 2016 at 5:49 am
I did this recently for a hierarchy showing a different measure at each level. For what I did, I used a combination of "IF" and "ISFILTERED". There are other related functions such as 'ISCROSSFILTERED', 'HASONEVALUE', 'HASONEFILTER' and possibly more that you should also look into since they may meet your needs better.
Here's the pseudocode for what I did:
IF(ISFILTERED ([Lowest Dimension Attr]), [Lowest Measure],
IF(ISFILTERED([Next Dimension Attr]), [Next Measure], etc.
))
March 21, 2016 at 10:44 am
Hi Brian,
Thanks for reply.
The pseudocode for what I need reads requires I used Lowest Logic instead of Measure:
IF(ISFILTERED ([Lowest Dimension Attr]), [Lowest Logic],
IF(ISFILTERED([Next Dimension Attr]), [Next Logic], etc.
))
EG.
=if(ISFILTERED(Resources[ResourceName]), SUM(TotalResourceTypePlannedRevenue),
IF(ISFILTERED('Cost Types'[CostType]), SUM(TotalCostTypePlannedRevenue), etc. )
I get error "The <name of model> measure group was not found." but I am not supplying measures, am wanting to supply logic eg. SUM() to be applied at different levels of hierarchy.
?
--Quote me
March 21, 2016 at 11:25 am
This morning, I wasn't at my work computer so I didn't have access to the code. Here is the literal code I used:
Combined Units:=IF(
ISFILTERED(DimDate[WeekOfMonth]) || ISFILTERED(DimDate[WeekOfQuarter]) || ISFILTERED(DimDate[WeekOfYear]) || ISFILTERED(DimDate[Week Name]) || ISFILTERED(DimDate[FirstDayOfWeek]),
[POS Units],
IF(
ISFILTERED( DimAccount[AccountName]) || ISFILTERED(DimMerchant[MerchantName]),
[Account Units],
[SAP Units]))
Each of the measures is whatever you define it as. For me, it was a sum of different columns in different fact tables. (The purpose was that the fact tables had different granularity and we wanted to show the best data available based on the filters.)
Does this help?
March 21, 2016 at 2:01 pm
Hi Brian,
Questions about your formula.
First section:
Are you getting sum of [POS Units] for each level of the Calendar hierarchy? Is [POS Units] a measure or the name of an attribute in your fact table? What does the symbol || denote?
Second section, what is the hierarchy you are referencing and what is the calculation?
ISFILTERED( DimAccount[AccountName]) || ISFILTERED(DimMerchant[MerchantName]),
[Account Units],
[SAP Units]))
--Quote me
March 21, 2016 at 2:53 pm
A little background on this cube - Most of the data is pre-aggregated to the monthly level. The POS Units are at the weekly level. There is no day level in the time dimension for this cube.
So, if the user has a filter down to the week level, POS Units is shown. [POS Units] is a measure and it is the sum of the underlying "Store Sales" fact table "POS Units" column.
Double pipe (||) is the "OR" statement so if any of these filters have been applied then it evaluates to true.
Second part, if the user has filtered by the Account Name or Merchant Name columns, then the [Account Units] measure is the sum of a different units column from a different fact table. This other fact table has a relation to both the DimAccount and DimMerchant dimensions. They happen to be one column dimensions (Account Name & Merchant Name).
In all other cases, the "SAP Units" are shown which is the most accurate measure in this case. This is from a third fact table.
In this example, the different logic for each measure it to get it from different tables. However, this could be any DAX logic that you want.
March 22, 2016 at 1:03 am
Hey, I think that's working, Brian.
PlannedRev:=if(ISFILTERED(Engagements[EngagementName]), SUM([SubconFFLTDRevenue]), IF(ISFILTERED('Project Types'[ProjectType]), SUM([SubconTnMLTDRevenue]),100))
--Quote me
March 22, 2016 at 5:40 am
That's great!
March 22, 2016 at 5:55 pm
ja, it is great Brian!
against forum etiquette...
Can you give me one more help?
Trying to embed a CASE statement inside the formula, but can't get even the simple case working as a standalone. Do you see what is wrong with my CASE syntax?:
switchTest:=SWITCH('POEC Delivery Types', "MCS T&M", SUM([one thing]),'POEC Delivery Types', "Subcon T&M", SUM([another thing]),0)
I'm trying to say that if the Fact column called POECDeliveryType has value "MCS T&M" then SUM([one thing]), and if the Fact column called POECDeliveryType has value "Subcon T&M", then SUM([another thing]), else use value 0.
<error>
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
--Quote me
March 23, 2016 at 6:03 am
Hi Polkadot, I supplied an answer for this on the other thread.
March 23, 2016 at 9:53 am
Thanks Brian.
BTW, I attended a PowerPivotPro users meeting in Redmond yesterday and regarding ISFILTERED syntax for applying logic at different levels of hierarchy, I was advised as best practice to always supply the lowest hierarchy item as the first ISFILTERED condition, and to travel up the hierarchy.
I have not actually testing to see if this made a difference for me but I thought I would post this in case someone else reads.
--Quote me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply