cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Distinct count dax with a measure filter

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.

 

Many thanks,

 

H

5 REPLIES 5
Highlighted
Solution Sage
Solution Sage

Re: Distinct count dax with a measure filter

@hidenseek9

 

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:

11.PNG

Table Mapping:

22.PNG

 

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])) 

33.PNG

 

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.

 

44.PNG

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 .

 

Thanks,
Xi Jin.

Highlighted
Helper V
Helper V

Re: Distinct count dax with a measure filter

@v-xjiin-msft

 

Allow me to share this data.

 

Sample 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.

 

2018-01-24 17_57_05-TEMPO Data_Test - Power BI Desktop.png

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.

2018-01-24 17_58_43-TEMPO Data_Test - Power BI Desktop.png

 

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.

2018-01-24 18_01_07-TEMPO Data_Test - Power BI Desktop.png

 

2018-01-24 18_00_14-TEMPO Data_Test - Power BI Desktop.png

 

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.

 

2018-01-24 18_17_09-TEMPO Data_Test - Power BI Desktop.png

 

2018-01-24 18_17_53-TEMPO Data_Test - Power BI Desktop.png

 

In unlisted store tab, it shows 3 stores that do not carry BIO.

2018-01-24 18_20_03-TEMPO Data_Test - Power BI Desktop.png

1569-3=1566

 

 

Appreicate your support!

 

If you have any question, please let me know.

 

Many thanks,

 

H

Highlighted
Solution Sage
Solution Sage

Re: Distinct count dax with a measure filter

@hidenseek9

 

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.

 

111.PNG

 

Thanks,
Xi Jin.

Highlighted
Helper V
Helper V

Re: Distinct count dax with a measure filter

@v-xjiin-msft

 

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.

  • Brand distribution for each team. (=373/1569=23.77%)
  • SKU distribution in a number of stores in the region (38/49=77.55%)

 

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.

Updated sample data

 

2018-01-26 08_28_25-TEMPO Data - Power BI Desktop.png

 

If you could help with this, it would be a tremendous help.

 

Many thanks,

 

H

Highlighted
Helper V
Helper V

Re: Distinct count dax with a measure filter

Could anyone help me with above question?

 

Thanks,

 

H

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors