Conditionally Supress blank columns in drill down

[aashritsingh]

Hello,

We are trying to dynamically supress columns with blank values in a drill down report. Here is a dummy example of a use case similar to ours:

So, our raw data is a single table like above. Here, Region 1 records will only have Amounts 1-4 populated and Amounts 5-6 will always be blank. Smilarly, for Region 2, Amounts 5-7 will be populated and rest will always be blank.

Our main report will have aggregations by region :

From here the users can drill into record detail for any region.

Is there a way to dynamically condition the drill down report to show only the amount columns with values amd hide the blank ones?

Drilling into Region 1 will only show Amounts 1-4 like the screenshot below:

And Region 2 will show Amounts 5-7 like this:

It would be idea to have this in a single drill down report rather than having multiple tabs. 

Note that our actual data will have around 25 Region values so maintaing separate tabs will be cumbersome.

Please advice on the most efficient way to accomplish this.

Thanks,

Aashrit

[v-karpurapud · Accepted solution]

Hi @aashritsingh 
Thank you for reaching out to the Microsoft Fabric Community Forum.

 I have attached the PBIX fille.  Please review it and let me know if you need any further assistance.

I hope this meets your requirements.

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

Thank You!

[lbendlin]

The problem is not with the Region values. It is with the Amount "columns" (which are measures, I guess).  You would need to create and maintain a disconnected reference table with all possible Amount name combinations, and then replace these measures with a single measure based on the table column.