The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi everyone,
I have a matrix table with a multiple row hierarchy. All the rows should appear like usual, even if the pertinent values are blank or 0. Except for the last row in the hierarchy, if that has blank or 0, it should not appear.
For example:
Drilldown from Journey | Fuel used |
- Journey 2024001 | 270 |
- Leg 1 start: 16-02-2024 | 170 |
Main engine | 150 |
Auxiliary engine | 20 |
- Leg 2 start: 27-02-2024 | 100 |
Main engine | 100 |
Auxiliary engine | 0 |
- Journey 2024002 | 250 |
- Leg 1 start: 08-03-2024 | 250 |
Main engine | 200 |
Auxiliary engine | 50 |
Leg 2 start: 12-03-2024 | 0 |
Main engine | 0 |
Auxiliary engine | 0 |
+ Journey 2024003 | 200 |
+ Journey 2024004 | 250 |
+ Journey 2024005 | 150 |
Highlighted red is what I'd like not to appear in the matrix table.
Any help would be much appreciated, thanks!
Solved! Go to Solution.
@AUDISU Thanks for your contribution on this thread.
Hi @GKJARC ,
You can follow the steps below to get your expected result, please find the details in the attachment.
1. Create a measure as below
Measure = IF ( SUM ( 'Table1'[Amount] ) = 0, BLANK (), SUM ( 'Table1'[Amount] ) )
2. Replace the field in Values option with the above new measure just as below screenshot
Best Regards
Hi @v-yiruan-msft ,
The help is appreciated however it is actually not a solution to what was asked. Only the level of consumption should be filtered, not the legs themselves. The offered solution removes also the legs if there is 0 consumption. as you can see, the leg of 12-3 is entirely missing from the visual with the proposed measure.
The legs should be shown even when there has been no consumption in a leg, it is still an event that is part of a voyage therefore it is in this case important that it remains visible.
@AUDISU Please find the PBIX sample file with the following WeTransfer link:
https://we.tl/t-qxX9wcgi5N
@AUDISU Thanks for your contribution on this thread.
Hi @GKJARC ,
You can follow the steps below to get your expected result, please find the details in the attachment.
1. Create a measure as below
Measure = IF ( SUM ( 'Table1'[Amount] ) = 0, BLANK (), SUM ( 'Table1'[Amount] ) )
2. Replace the field in Values option with the above new measure just as below screenshot
Best Regards
Hi @v-yiruan-msft ,
The help is appreciated however it is actually not a solution to what was asked. Only the level of consumption should be filtered, not the legs themselves. The offered solution removes also the legs if there is 0 consumption. as you can see, the leg of 12-3 is entirely missing from the visual with the proposed measure.
The legs should be shown even when there has been no consumption in a leg, it is still an event that is part of a voyage therefore it is in this case important that it remains visible.
Hi @GKJARC ,
You can achive this using INSCOPE function.
Please share sample data. Then I can create DAX query exactly you want.
Thanks.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
62 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |