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

 

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 

 

Let me first appreciate and thank you for coming back to me with an updated solution, I believe you have got the right level of the data and the understanding of the desired output.

 

The solution needs to be in a single line visual, where if i select Level 1 from the matrix the lline visual should give me level 2 and if I select Level 2 the line visual should give me level 3 but in the same visual. So the legend needs to be dynamic I tried to use the logic which you sent in your previous reply but measure wont output multiple values it needs to be scalar. (Possible solution I am trying at my end: - Pivot the data and use some keys)

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

    Hi NimaiAhluwalia,

     

    Thanks for your patience! Since a measure cannot return multiple values for a legend, we need a different approach. Here are two effective ways to solve this:

     

    Reshape Data Model : Transform the dataset so that Level 1, Level 2, and Level 3 are stored in a single column rather than separate columns. This allows Power BI to filter dynamically and display the correct categories in the legend.

     

    Alternative (Field Parameters): If reshaping data isn't possible, use a field parameter to switch between levels dynamically in the same visual. This allows users to toggle between levels but won’t create a fully dynamic legend.

     

    Additionally, a disconnected table can be used as a slicer to help users control which level appears in the visual.

     

    A community discussion on dynamic legends using Field Parameters might help:
    Dynamic Legends using Field Parameters

    Alternatively, restructuring your data (pivoting) and using a disconnected table could also work.

    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