Forum Discussion

NimaiAhluwalia's avatar
NimaiAhluwalia
Continued Contributor
1 year ago
Solved

Next Level display

Hello All,   I am trying to solve a use case where I have a single fact table, which has the below information:   Differet Levels, amount and year.     I will be putting the above data i...
Need Help
Show and Tell
Tutorial Requests
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    Hi NimaiAhluwalia,
    Thank you for reaching out to the Microsoft fabric community forum. Thank you lbendlin, for your inputs on this issue.


    After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.

    Create NextLevelMeasure in the FactTable:

    NextLevelMeasure =

    VAR SelectedLevel = SELECTEDVALUE('FactTable'[Level])  -- Get selected level

    VAR NextLevels =

        FILTER(

            'MappingTable',

            'MappingTable'[Parent Level] = SelectedLevel  -- Find next levels for the selected level

        )

    VAR NextLevelNames = VALUES('MappingTable'[Next Level])  -- Get the names of next levels

     

    RETURN

        CALCULATE(

            SUM('FactTable'[Amount]),

            'FactTable'[Level] IN NextLevelNames  -- Filter fact table to next levels

        )


    I am also including .pbix file for your better understanding, please have a look into it:


    I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

    Thank you for using Microsoft Community Forum.

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
1 year ago

Hi NimaiAhluwalia,
Thank you for reaching out to the Microsoft fabric community forum. Thank you lbendlin, for your inputs on this issue.


After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.

Create NextLevelMeasure in the FactTable:

NextLevelMeasure =

VAR SelectedLevel = SELECTEDVALUE('FactTable'[Level])  -- Get selected level

VAR NextLevels =

    FILTER(

        'MappingTable',

        'MappingTable'[Parent Level] = SelectedLevel  -- Find next levels for the selected level

    )

VAR NextLevelNames = VALUES('MappingTable'[Next Level])  -- Get the names of next levels

 

RETURN

    CALCULATE(

        SUM('FactTable'[Amount]),

        'FactTable'[Level] IN NextLevelNames  -- Filter fact table to next levels

    )


I am also including .pbix file for your better understanding, please have a look into it:


I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

NimaiAhluwalia's avatar
NimaiAhluwalia
Continued Contributor
1 year ago

Hello v-kpoloju-msft 

 

Thanks for providing the idea for working towards the solution, Looking at my data model and the table I came on to a concultion that we need a mapping table logic which you propesed in the above solution.

 

And just a quick note on that if we want to optimise the use case more further, from the measure which you have sent which is taking the input from the fact table - (VAR SelectedLevel = SELECTEDVALUE('FactTable'[Level])  -- Get selected level) Can we do something wher instead of the measure looking on the table it can actually take the input from the matrix I know the Level column used in the matrix and the fact table are the same but what if we have 2 fact tables and I want my measure to take inputs from the matrix visual which is built on first fact table and the line chart built on a second fact table.

 

Thanks