Hello Power BI community!
I have a problem with distinctcount dax with a measure filter.
For instance, I am trying to understand a number of stores that that our product is not listed.
To understand a list of stores that our product is not listed,
I created a measure that calculate average product price of all product sold at stores,
if the average price is 0, then those stores do not carry our product.
So to understand a number of stores that do not carry our product,
I created a below measure but it does not work.
Unlisted Store Count = CALCULATE(DISTINCTCOUNT('Tempo Data (Historical)'[Store Name]),FILTER('Tempo Data (Historical)',[Average Product Price]=0),ALL(Mapping[Product Name]),ALL(Mapping[Brand]),ALL(Mapping[Brand Product]))
I do not want this store count measure to be affected when I select certain filter such as product name,
so I am using all dax formula.
Would be great if someone can offer an advise to resolve this issue.
Since you didn’t share us your sample data. So I have made some assumption. Please refer to following sample, see if it works for you:
My assumption sample data is like this:
Table Tempo Data:
I have used this DAX expression to calculate the average product price measure for each store:
Average Product Price = CALCULATE(AVERAGE('Tempo Data'[Product Price]),FILTER(ALLSELECTED('Tempo Data'),'Tempo Data'[StoreName]=MAX('Tempo Data'[StoreName])))
Also, I have created another measure and a calculated column with the same expression (which you shared) for Unlisted Store Count:
CALCULATE(DISTINCTCOUNT('Tempo Data'[StoreName]),FILTER('Tempo Data',[Average Product Price]=0),ALL(Mapping[Product Name]),ALL(Mapping[Brand]))
As you can see, I can get the unlisted distinct store count. And when use slicer to filter the Product Name or Brand, the Unlisted Store Count measure will be changed however calculated column will not.
As you said that “I do not want this store count measure to be affected when I select certain filter such as product name”. I think you can try calculated column instead of measure.
If above sample doesn’t satisfy your requirement, please share us more information like your source table structure and some sample data and your desired result .
Allow me to share this data.
In Tempo Data (Historical) dataset, I have appended all monthly store data from 2016.
I have applied below filter in the page as you can see.
I want to understand the total number of stores in the data
in November 2017, for AEON team and for BIO brand.
I know that the total number of stores is 1,569, so the measure is working.
I want to exclude the unlisted stores in another store count measure.
If I do, I should get 1,566.
This measure works as long as I do not select more filters.
However, if I select a particular product in a filter,
then the measure does not work anymore.
(i.e. BIO 4P Aloe)
What I do not understand is that in the measure, I have put a filter
ALL('Tempo Data (Historical)'[SKU])
so I should still get 1,566.
In unlisted store tab, it shows 3 stores that do not carry BIO.
Appreicate your support!
If you have any question, please let me know.
Thanks for sharing us your report. It helps us a lot to understand your requirement.
Then according to your description and your report. I think there's no need to modify the measures to achieve your requirement. It will be pretty complicated to change the measures.
To achieve your requirement, you can try Eidt interactions between the Slicer and Card visual. Choose SKU Slicer go to Format => Edit interactions and set the Brand Distribution card to none. Then the filter will no longer affect this card. Same to others.
Thank you for your response.
It works great with showing the distribution number in a card visual.
However, if I want to calculate the distribution % of the total number of stores
how can I achieve this?
For instance, I edited the Edit Interaction to show below distribution and this is great.
In below, I would like to calculate the following.
However, I am having a difficulty because I only changed the edit interaction so
the measure itself calculates a different number, so the distribution percentage comes out wrong.
If you could help with this, it would be a tremendous help.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.