The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
I have a table which I want to filter based on various combinations of Domiciles, without creating a long IF statement to determine all of the possible combinations - my DAX measure at the moment has VAR for all of the following: "Ghana", "India", "Pakistan" "Rest Of World":
VAR Ghana =
CALCULATE(
SELECTEDMEASURE()
, Domicile[CountryOfDomicile] = "Ghana"
)
I then have a RETURN statement which is an IF statement, but only adds them together or allows for each individual at a time
RETURN
IF(
SELECTEDVALUE( Domcile[CountryOfDomicile] ) = "India"
, India
, IF(
SELECTEDVALUE(Domcile[CountryOfDomicile] ) = "Ghana"
, Ghana
, IF(
SELECTEDVALUE( Domcile[CountryOfDomicile] ) = "Pakistan"
, Pakistan
, IF(
SELECTEDVALUE( Domcile[CountryOfDomicile]) = "Rest Of World"
, RestOfWorld
, India + Pakistan + Ghana + RestOfWorld
)
)
)
)
I would like a way to instead have a RETURN which allows for any combinations of the above (eg Ghana + Rest Of World, Ghana + Pakistan etc) depending on the end user's selection in the slicer.
All suggestions gratefully received!
@Anonymous , if Rest of the world is just a value and do have data for it in table, then use measure
If( "Rest Of World" in values(Domicile[CountryOfDomicile]) ,CALCULATE( SELECTEDMEASURE(), all()), SELECTEDMEASURE())
if "Rest of the world" also has data in your table, then only SELECTEDMEASURE() should do
Thank you for replying.
Rest of World is a value, the same as Ghana etc - each row in the table represents an individual, and the domicile field contains one of these values.
The complication is that I want to show the sum of the data according to what the end user selects, which might not just be Ghana or Rest Of World but a combination of these (or any others)
User | Count |
---|---|
65 | |
60 | |
55 | |
54 | |
31 |
User | Count |
---|---|
180 | |
88 | |
72 | |
48 | |
46 |