Forum Discussion
Path Measure
- 1 year ago
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 ) )
Hi MStark,
Although [Path P&L PPD] doesn’t use Level 4 directly, it depends on Level 1 and Level 2 from the Path table. Using the custom Level 4 from the Data table disrupts that filter context. Consequently, SELECTEDVALUE('Path'[Level 1]) may return blank, and the SWITCH logic fails to match anything, causing the measure to return nothing beyond Level 3.
Please update your [Path P&L PPD] measure to check if the path levels are available, and if not, use a fallback calculation.
Dax Measure:
Path P&L PPD =
VAR Level1 = SELECTEDVALUE('Path'[Level 1])
VAR Level2 = SELECTEDVALUE('Path'[Level 2])
VAR IsPathAvailable = NOT ISBLANK(Level1) && NOT ISBLANK(Level2)
VAR Amount = [Custom Amount2]
VAR MatchingCensus = [MatchingCensus]
VAR TotalCensus = [Census]
VAR DaysInMonth = 28
RETURN
IF(
IsPathAvailable,
SWITCH(
TRUE(),
Level1 = "Census", DIVIDE(Amount, DaysInMonth),
Level1 = "Revenue" && Level2 = "Revenue Current", DIVIDE(Amount, MatchingCensus),
TRUE(), DIVIDE(Amount, TotalCensus)
),
-- Fallback when Level1/Level2 are not available (e.g., custom Level 4)
DIVIDE(Amount, TotalCensus)
)
This keeps your current logic for Levels 1–3, but also ensures the measure doesn’t break when you're using the custom field at Level 4.
If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!
Thank you and continue using Microsoft Fabric Community Forum.
- MStark1 year ago
Helper III
Thanks v-sgandrathi for looking into this
The issue with the measure you provided is that level 1 and level 2 are not blank for any levels. When I added a measure to debug, all rows generated a true (it does generate every account under each level.. not sure if that has anything to do with the issue)