Forum Discussion

nele's avatar
nele
Frequent Visitor
4 years ago
Solved

Distinctcount filtered by measure

Hi,

 

I want to use the following expression:

Partners pos Delta = CALCULATE(DISTINCTCOUNT('Store Performance'[Customer Partner Name]),FILTER('Store Performance',[salesDelta]>0))

However, the "Partners pos Delta"-measure doesn't give the correct result. In the example below I have 1 out of 3 stores with a positive sales delta. So I want to count 1 row. The "Partners pos Delta"-measure gives me 3. I think this is because the [salesDelta] is not calcultated correctly. The [salesDelta] is another measure that I created before. It is our sales evolution (%) minus the sales evolution of the category (%). This is correctly calcultated per store (as can be seen in the table below), but when I want to count the stores with a positive salesDelta, I think my DAX expression doesn't take into account that it needs to calculate the salesDelta per store and then needs to count the stores with a positive one.
 

 

I hope someone can help me with this problem. Thank you in advance!

  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    nele Try:

    Partners pos Data = 
  VAR __Table =
    FILTER(
      ADDCOLUMNS(
        SUMMARIZE('Store Performance',[Customer Partner Name]),
        "__salesDelta",[salesDelta]
      ),
      [__salesDelta]>0
    )
RETURN
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(__Table,"Customer Partner Name",[Customer Partner Name])
    )
  )

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    nele Try:

    Partners pos Data = 
  VAR __Table =
    FILTER(
      ADDCOLUMNS(
        ALLSELECTED('Store Performance),
        "__salesDelta",[salesDelta]
      ),
      [__salesDelta]>0
    )
RETURN
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(__Table,"Customer Partner Name",[Customer Partner Name])
    )
  )
    • nele's avatar
      nele
      Frequent Visitor
      4 years ago

      Greg_Deckler , Thank you for your quick response! Unfortunately, it still gives me the same outcome.

       

       

    • nele's avatar
      nele
      Frequent Visitor
      4 years ago

      Greg_Deckler , maybe I need to add that the [salesDelta] is calculated per "Customer Partner Name", but this is not based on 1 row. There are multiple rows for 1 partner and based on the sum of these rows, the [salesDelta] is calculted. I think this is the reason that I cannot just add a column "salesDelta" in my dataset, since the average of the individual salesDelta's is not the same as the salesDelta of the sum of the individual rows.