Filter matrix only 1 row in matrix

[GKJARC]

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.

• 'Leg 2 start: 27-02-2024' should only show the row of Main engine, as the Auxiliary engine has used 0 fuel in this leg.
• 'Leg 2 start: 12-03-2024' should not be expandable at all (if possible), because both underlying engines have used no fuel. I do need the leg itself to remain visible even though the total fuel used for this leg is 0.

Any help would be much appreciated, thanks!

[v-yiruan-msft · Accepted 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

[GKJARC]

@AUDISU Please find the PBIX sample file with the following WeTransfer link:
https://we.tl/t-qxX9wcgi5N

[v-yiruan-msft · Accepted 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

[GKJARC · Accepted solution]

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]

Hi @GKJARC ,
You can achive this using INSCOPE function.
Please share  sample data. Then I can create DAX query exactly you want.
Thanks.