cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shekhar_shres
Helper I
Helper I

Return Blank Value if a Slicer is selected

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

RegionState ID
Region AA1
Region BB1
Region CC1
Region XX2
Region YY2
Region ZZ2

 

And I have StateTable which is connected to Region Table above with State ID

StateIDState
1VIC
2NSW


Also, I have as staff cost table (factTable) as below.

StateIDMetricAmount
1Staff Cost500

 

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,






1 ACCEPTED SOLUTION
vicky_
Solution Supplier
Solution Supplier

You could try using ISFILTERED(dimRegion[Region]) instead of HASONEVALUE() in your if statement.

View solution in original post

2 REPLIES 2
vicky_
Solution Supplier
Solution Supplier

You could try using ISFILTERED(dimRegion[Region]) instead of HASONEVALUE() in your if statement.

Exactly what I needed.


Thank you

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.