Hi everyone,
I hope you are all doing great. I want to make a Measure goes blank if a slicer is selected but I am only half way there.
I have dimRegion Table as below:
RegionState RegionState ID
| Region | State ID |
| Region AA | 1 |
| Region BB | 1 |
| Region CC | 1 |
| Region XX | 2 |
| Region YY | 2 |
| Region ZZ | 2 |
And I have StateTable which is connected to Region Table above with State ID
Also, I have as staff cost table (factTable) as below.
| StateID | Metric | Amount |
| 1 | Staff Cost | 500 |
The problem is Staff Cost is allocated on State Level and not the Region Level. So, I want to make sure that my measure
(StaffCost = sum(StaffCost[Amount]) )goes blank or 0 when someone selected Region Slicer in Power BI.
I tried using following dax
StaffCostDynamic = IF(HASONEVALUE(dimRegion[Region]),blank(), [StaffCost]))
So far, the above dax works if someone selects only one Region (Say Region AA) in the slicer. but as soon as someone selects two Region in the Slicer (Say Region AA and Region BB) then the actual value appears (In our example $500).
What dax should I write to ensure that the StaffCostDynamic Measure goes blank when someone selected Region Slicer.
Your help will be highly appreciated.
Kind regards,
