Distinct count dax with a measure filter

[hidenseek9]

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

[v-xjiin-msft]

@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:

Table Mapping:

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 .

Thanks,
Xi Jin.

[hidenseek9]

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

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.

1569-3=1566

Appreicate your support!

If you have any question, please let me know.

Many thanks,

H

[v-xjiin-msft]

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

Thanks,
Xi Jin.

[hidenseek9]

@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

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

Many thanks,

H

[hidenseek9]

Could anyone help me with above question?

Thanks,

H