Path Measure

[MStark]

Hi,

Getting stuck with this measure and hoping someone can assist. Have a path table that has child, parent, path and the different path items. When I create a matrix with different levele and then put in the amount and PPD measures below it works. Thing is for level 4 want field from data table instead of level 4 for specific scenerios. So created a custom level 4 in data table but when I use that, PPD measure doesnt generate past level 3. Any suggestions on what Im doing wrong and how I can fix?

Custom Amount2 =

VAR Level1 = SELECTEDVALUE('Path'[Level 1])

VAR Level2 = SELECTEDVALUE('Path'[Level 2])

VAR OperatingDeps = {

    "Nursing and Medical",

    "Social Services",

    "Therapy and Ancillary",

    "Recreation",

    "Food and Nutrition"}

Var Census=CALCULATE(SUM(Data[Amount]),TREATAS({"Census"},'Path'[Level 1]))

Var RB=CALCULATE(SUM(Data[Amount]),TREATAS({"Revenue Current","Revenue Adjustments"},'Path'[Level 2]),REMOVEFILTERS('Path'))

Var Rev=CALCULATE(SUM(Data[Amount]),TREATAS({"Revenue"},'Path'[Level 1]), REMOVEFILTERS('Path'))

Var OperatingExp=CALCULATE(SUM(Data[Amount]),TREATAS(OperatingDeps,'Path'[Level 2]),REMOVEFILTERS('Path'))

VAR ManagementExp=CALCULATE(SUM(Data[Amount]),TREATAS({"Management Expenses"},'Path'[Level 1]),REMOVEFILTERS('Path'))

RETURN

SWITCH(    TRUE(),

    Level2="Total R&B Revenue",RB,

    Level1="EBITDAR", Rev+OperatingExp+ManagementExp,

    SUM(Data[Amount]))

Census = CALCULATE(SUM(Data[Amount]),TREATAS({"Census"},'Path'[Level 1]),REMOVEFILTERS('Path'))

MatchingCensus =

VAR SelectedLevel3 = LOWER(TRIM(SELECTEDVALUE('Path'[Level 3])))

VAR TotalCensus = [Census]

RETURN

IF(    NOT ISBLANK(SelectedLevel3),

    CALCULATE( SUM('Data'[Amount]),

        FILTER( ALL('Path'),     'Path'[Level 1] = "Census"      &&     LOWER(TRIM(SUBSTITUTE('Path'[Level 2], " Census", ""))) = SelectedLevel3

        )),TotalCensus)

Path P&L PPD =

VAR Level1 = SELECTEDVALUE('Path'[Level 1])

VAR Level2 = SELECTEDVALUE('Path'[Level 2])

VAR Amount = [Custom Amount2]

VAR TotalCensus = [Census]

VAR MatchingCensus = [MatchingCensus]

VAR DaysInMonth = 28

RETURN

    SWITCH(

        TRUE(),

        Level1 = "Census",

            DIVIDE(Amount, DaysInMonth),

        Level1 = "Revenue" && Level2 = "Revenue Current",

            DIVIDE(Amount, MatchingCensus),

        TRUE(),

        DIVIDE([Custom Amount2],[Census]))

I cant add level 1-3 to data table since there are subtotals in the path that are not in the table

 Any assistance would be appreciated! Thanks in advance!!

[MarkLaf · Accepted solution]

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
    )
)

[MStark]

@MarkLaf Thank you SO much!! Your measure works!! The only thing I added was keepfilters so that can have the dates and location filtered as well

Path P&L PPD fixed =

VAR Level1 = SELECTEDVALUE('Path'[Level 1])

VAR Level2 = SELECTEDVALUE('Path'[Level 2])

VAR Amount = [Custom Amount2]

VAR MatchingCensus = [MatchingCensus]

VAR DaysInMonth = 28

VAR TotalCensus =

    CALCULATE(

        SUM(Data[Amount]),

        TREATAS({"Census"}, 'Path'[Level 1]),

        REMOVEFILTERS('Path'),

        REMOVEFILTERS(Data),

        KEEPFILTERS(VALUES(Data[Posting_Date])),

        KEEPFILTERS(VALUES(Data[Location_Name]))    )

RETURN

    SWITCH(

        TRUE(),

        Level1 = "Census", DIVIDE(Amount, DaysInMonth),

        Level1 = "Revenue" && Level2 = "Revenue Current", DIVIDE(Amount, MatchingCensus),

        DIVIDE(Amount, TotalCensus)

    )

Ive been playing around with this for over 3 days and couldnt figure it out (chatgpt also tried but couldnt get it;)) Really appreciate you taking the time to look at this and figure it out!!

[v-sgandrathi]

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.

[MStark]

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)

[v-sgandrathi]

Hi @MStark,

Has your issue been resolved?If the response provided by @Nasif_Azam addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[Nasif_Azam]

Hey @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

[MStark]

@Nasif_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