Forum Discussion
Helper IIIPath Measure
Troubleshoot the particular filter context you are concerned with, which are:
Path[L1] = Expense, Path[L2] = Nursing and Medical, Path[L3] = Nursing Other, Data[L4] = {PR Dental Ins, PR WC Ins, Supplies}
Path[L1] = Expense, Path[L2] = Nursing and Medical, Path[L3] = Nursing Wages, Data[L4] = {DC, NDC}
Looking at your measures:
- In Path P&L PPD, you have a switch with two options and then a default fallback. The two options' criteria are not met, so we are calculating the fallback: DIVIDE([Custom Amount2],[Census])
- In Custom Amount2, we likewise have a switch with two options and a default fallback. And again, the first two options' criteria are not met, so we are calculating the fallback: SUM(Data[Amount])
- In Census, there are no conditionals and we just have the straight calculation: CALCULATE(SUM(Data[Amount]),TREATAS({"Census"},'Path'[Level 1]),REMOVEFILTERS('Path'))
In other words, we can simplify your question a bit into, why is the following blank for the filter contexts we enumerated at top?
Simplified = DIVIDE( SUM( Data[Amount] ), CALCULATE( SUM( Data[Amount] ), TREATAS( {"Census"}, 'Path'[Level 1] ), REMOVEFILTERS('Path') ) )For this to be blank, that means the numerator is blank, the denominator is blank, or the denominator is 0. Without seeing your data, we don't know for sure.
That said, here is my guess for what is the problem:
- Given that you seem to expect something to calculate here, probably the issue is the filter context on your denominator, specifically, we have Data[L4] in the filter yet you do not seem to be handling it in any way.
- As in, we have REMOVEFILTERS( 'Path' ), which ensures we ignore the Path-related filter context, but we still have filter context on Data.
- So, as is, the denominator calculation is saying, "give me the sum of Data[Amount] for all rows that fall under Path[L1] = Census (given TREATAS and REMOVEFILTERS) AND where Data[L4] = {DC, NDC, PR Dental Ins, PR WC Ins, Supplies} (given we aren't touching Data's filter context)."
- My guess is that none of your Data rows actually match this filter criteria and thus you are getting blank.
I suspect what you want in the denominator is just the total sum of Data[Amount] under Path[L1] = Census, so this can probably all be fixed by updating your Census measure:
Simplified_fixed? = DIVIDE( SUM( Data[Amount] ), CALCULATE( SUM( Data[Amount] ), TREATAS( {"Census"}, 'Path'[Level 1] ), REMOVEFILTERS('Path'), REMOVEFILTERS(Data) // <-- FIX HERE ) )
Super UserHey MStark ,
Thanks for sharing the context and screenshots. The core of your issue is:
Your [Path P&L PPD] measure works well until Level 3, but once you use a custom Level 4 from the Data table (instead of Path[Level 4]), the measure fails to calculate beyond Level 3.
Problem Overview
1. Hierarchy Mismatch
The [Path P&L PPD] measure and [Custom Amount2] are tightly coupled to the original 'Path' table hierarchy. The logic depends on: SELECTEDVALUE('Path'[Level 1]), SELECTEDVALUE('Path'[Level 2]), SELECTEDVALUE('Path'[Level 3])
When you replace Path[Level 4] with a custom field from the Data table (Level 4 Custom), the context for the hierarchy is broken because: The hierarchy isn’t fully from a single table and SELECTEDVALUE('Path'[Level N]) stops returning a value when lower levels are not from the same table.
Approach 1: Normalize Hierarchy to One Table
If possible, flatten the hierarchy so all levels (including the custom one) exist in a single table. If that's not feasible due to the subtotals you mentioned, then use a bridging logic.
Approach 2: Use LOOKUPVALUE to Link Path and Data
If your custom Level 4 field lives in 'Data' table and the relationship to 'Path' is via an ID or key (e.g. 'Path'[ID] = 'Data'[PathID]), then you can update your measures like this:
Update your [Custom Amount2]:
VAR Level1 = SELECTEDVALUE('Path'[Level 1])
VAR Level2 = SELECTEDVALUE('Path'[Level 2])
VAR Level4Custom = SELECTEDVALUE('Data'[Level 4 Custom])Then add logic like:
VAR CustomLevel4Check =
IF(
NOT ISBLANK(Level4Custom),
// your fallback logic for level 4, e.g., apply special calculation
CALCULATE(SUM(Data[Amount]), Data[Level 4 Custom] = Level4Custom),
// else use normal path logic
[original path logic]
)Approach 3: Pass Filter Context from Visual
If the visual includes both 'Path'[Level 1-3] and 'Data'[Level 4 Custom], but you need your measure to respond to 'Data'[Level 4 Custom], you can use ISINSCOPE:
VAR IsLevel4Custom = ISINSCOPE('Data'[Level 4 Custom])
RETURN
SWITCH(
TRUE(),
IsLevel4Custom, CALCULATE(SUM('Data'[Amount]), ALLEXCEPT('Data', 'Data'[Level 4 Custom])),
Level2 = "Total R&B Revenue", RB,
Level1 = "EBITDAR", Rev + OperatingExp + ManagementExp,
SUM(Data[Amount])
)
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
MStarkNasif_Azam Thank you for looking into this and responding!
As said in original post, Level 1-3 are from the path table as it includes rows that are subtotals that are not in the data table. If I bring those in to data table and use all levels from that table, I will be missing those rows which is the whole purpose of doing these paths
Approach 3 did not generate anything
I still dont fully understand the issue. I see thats its not generating PPD for rows after level 3 but dont understand why as 1- the amount measure is generating and 2- the PPD measure doesnt have anywhere to look at the levels past level three so dont even know what should update as dont see where its looking at the levels
Appreciate any assistance with this