Forum Discussion
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 in a matrix to visualise the amount value.
Problem: I want to create a line chart in such a way when I select any of the levels from the matrix the line chart should give me only the information on the next level.
Example: If I select cost of sales from the matrix visual the line chart should be filetered and give me the output as direct overheads, direct energy and Direct Labour only.
(possible solution: so basically I want to create a meaure in such way which can be inserted on the legend level of the visual and changes dynamically and gives me the next level for the Level selected from the matrix visual)
I am open to other solutions as well. eg (changing data model etc etc)
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.
9 Replies
Use the standard drilldown functionality in the line chart.
Hello lbendlin
I did thought of using it, but the client is not happy using the drilldown functionality, can you suggest any solution to achieve the above, I did some research I believe it might be possible using disconnected tables?
Don't think so. Challenge them on the use of a line chart. A Waterfall chart might be more appropriate.
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.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
Thanks v-kpoloju-msft for coming back to me.
Thanks for replicating the output I believe the data which you created is not how I have in my system. Please find the attached for better view of the data.
Problem: Use-case 1
If I select cost of sales from the matrix visual the line chart should be filetered and give me the output as direct overheads, direct energy and Direct Labour only on the legend which will be calculated againt the amount.
Problem: Use-case 2If I select direct overheads from the matrix visual the line chart should be filetered and give me the output as Level 2.7, Level 2.8 and Level 2.9 only on the legend which will be calculated againt the amount.
Problem: Use-case 3
If I select direct labour from the matrix visual the line chart should be filetered and give me the output as Level 2.4, Level 2.5 and Level 2.6 only on the legend which will be calculated againt the amount.
Expected solution 1 : When I select the cost of sales I should get the below image (currently I have manually generated the output to show the expected result)
Expected solution 2 : Likewise if I select Direct overheads I should see the below results.
In a nutshell whatever Level of data point I select from the matrix visual I must see the one below level of the data in the line chart. Please let me know if you need any more information.
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
