Forum Discussion

hidenseek9's avatar
hidenseek9
Icon for Post Patron rankPost Patron
8 years ago

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

Need Help

5 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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's avatar
      hidenseek9
      Icon for Post Patron rankPost Patron
      8 years ago

      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's avatar
        v-xjiin-msft
        Icon for Solution Sage rankSolution Sage
        8 years ago

        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.