Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone
I am hoping someone can help me with this .
Note - I have lots of experience using excel and have just started learning DAX.
Situation: Want to create a column that shows count of all stores regardless of what the slicer selection is.
Example: I know the count of stores that I own
Count of stores I own =
CALCULATE(
COUNT('Door_Dash'[ Site Number]),
'Door_Dash'[ Combination Name]
IN { "MY BRAND NAME" }
)
What I don't know is the count of all stores in the table, whether I own them or not.
Here is what I tried:
AllStores = CALCULATE( COUNT('Door_Dash'[ Site Number]) ,ALL('Door_Dash'[ Site Number]) )
This should work, but here is why this did not work. My slicer is from table 'Store Attributes'[Number/Region].
I have created a relationship between 'Store Attributes'[Number/Region] and 'Door Dash'[Site Number].
The problem (I think) is that my 'Store Attributes' table only has site numbers for the stores I own. It does not have site numbers for stores I do not own.
DESIRED OUTPUT: Count of all stores in the table, whether I own them or not, and not based upon slicer selection.
What is the correct DAX expression for this desired output?
Solved! Go to Solution.
hi, @Anonymous
Since My slicer is from table 'Store Attributes'[Number/Region]
If you want the formula to ignore slicer, you may add the condition into the formula like
AllStores = CALCULATE( COUNT('Door_Dash'[ Site Number]) ,ALL('Door_Dash'[ Site Number]) ,ALL('Store Attributes'[Number/Region]) )
If it is not your case, please share some data sample and expected output. Do mask sensitive data before uploading.
Best Regards,
Lin
hi, @Anonymous
Since My slicer is from table 'Store Attributes'[Number/Region]
If you want the formula to ignore slicer, you may add the condition into the formula like
AllStores = CALCULATE( COUNT('Door_Dash'[ Site Number]) ,ALL('Door_Dash'[ Site Number]) ,ALL('Store Attributes'[Number/Region]) )
If it is not your case, please share some data sample and expected output. Do mask sensitive data before uploading.
Best Regards,
Lin
Hi @v-lili6-msft that worked great, so I was close just need an expert like you to help out!
-------
Ok, can you help me with one more question (staying within the theme of this thread):
I also need an expression that returns all stores IN the slicer that I do not own. So far I have:
Count of stores I own = CALCULATE( COUNT('Door_Dash'[ Site Number]), 'Door_Dash'[ Combination Name] IN { "MY BRAND NAME" } )
And thanks to you I have:
AllStores = CALCULATE( COUNT('Door_Dash'[ Site Number]) ,ALL('Door_Dash'[ Site Number]) ,ALL('Store Attributes'[Number/Region]) )
And my slicer is:
What I need is an expression that counts all the stores IN the slicer that I DO NOT own.
What I have so far:
Count of stores I DO NOT own = CALCULATE( COUNT('Door_Dash'[ Site Number]), 'Door_Dash'[ Combination Name] <> { "MY BRAND NAME" } )
Unfortunately this is returning (Blank). Any ideas?
hi, @Anonymous
Does { "MY BRAND NAME" } is a data list or a fixed value?
If it is a data list, you need use NOT IN for your formula. like
Count of stores I DO NOT own = CALCULATE( COUNT('Door_Dash'[ Site Number]), NOT('Door_Dash'[ Combination Name]) IN { "MY BRAND NAME" } )
For example:
then
Measure = CALCULATE(COUNTA(Table1[Column1]), NOT( Table1[Column1]) in {"b","c"})
If it is just one fixed value, you don't need to use { } in your formula. like
Count of stores I DO NOT own = CALCULATE( COUNT('Door_Dash'[ Site Number]), 'Door_Dash'[ Combination Name] = "MY BRAND NAME" )
Also If you do want the formula to ignore slicer, you could add the condition into the formula
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |