Need help solving a DAX expression in my Power BI Report

[Anonymous]

Hi!

I have a little dax problem in power bi I want to solve. To make it simple to explain, I have a fact table called Fact and 3 dimensions (Region, Location and Unit) that are related to the fact table. In theory, there are a hierarchy between the dimensions (Region->Location->Unit), but this is "hidden" in the fact table. So my report has three slicers, each for one of the dimensions. The basic way to use the report is to first select one (or more) region(s), next one of the locations available for the region selected. In the report there are some measures too. For simplicity, I have added only one called Measure, which justs summarize a column in the fact table. The dataset returned I will use as an input to a custom R visual, but that is not the thing now.

Basically, the behavior is like this: when I select a Region in the slicer, it should create a dynamic dataset returning the Region itself and all underlying Locations with the measure calculated. Now, the Location filter is reduced to the set of Locations available. Then I pick a Location (or more), and now the dataset returned is the Location selected and all Units related to the Location.

Below I have created a sample report. Of course the Hierarchy Slicer is propably a better slicer choice. Therefore I have used it in the example, but I could had used the other slicers as well. The pictures below is trying to explain. The dataset should return (Region, Location, Unit, Measure as a calculated table (using some kind of summarizecolumns).

When  no slicer selected, it should in this case return 4 rows (region North, West, South and East), Blank or "All" on Location and Unit, and the Measure.

All Regions (no selections)

When one Region selected it should in this case the Region itself on a row and all Locations for that Region, Unit will still be blank or "all"North Region selected

And when selecting a Location, it should in this case return the selected Location and all units related to that location (and the region of course)

Location A is selected

I hope you understand my case, but feel free to change the behavior if you have a better idea. There may be better data model, like using UNION either in DAX or in SQL View. Or unpivoting and so on. Feel free.

I have now added the demo PBIX file so you can experiment

PBIX demo file 

[V-lianl-msft]

Hi @Anonymous ,

Based on your description and the picture provided, slicer work properly. can you share a dummy pbix and the results you expect? So we'll be more clear of your scenario.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.