Forum Discussion

legrand's avatar
legrand
Helper I
8 years ago
Solved

Distinct count measure with filter

Hi,

I have a table with sales orderlines:

 

1

 

I have a related table via an item table with the manufacturer of each item (the item no is stored in the column "No")

 

I want to create a measure which counts the distinct values of the column "Document_No" (which stores the order number which has 1..n sales orderlines) if the following two criterias are met:

 

  • A "Document_No" has 5 sales orderlines of a specific Manufacturer (let's call the manufacturer "super duper")
  • A sales orderline of the manufacturer "super duper" has the "Line_Amount" = 0

If possible without creating another table which groups the sales orderlines by "Document_No".

 

Many thanks!

Need Help
  • srinivt's avatar
    srinivt
    8 years ago

    CALCULATE(CALCULATE(DISTINCTCOUNT(Sales[Document_No]), FILTER(ALL(Sales[Document_No]), CALCULATE(COUNT(Sales[Line_No])) = 5), Sales[Line_Amount] = 0), Sales[Manufacturer] = "Super Duper")

     

    OR

     

    CALCULATE(COUNTROWS(FILTER(VALUES(Sales[Document_No]), CALCULATE(COUNT(Sales[Line_No])) = 5)), Sales[Line_Amount] = 0), Sales[Manufacturer] = "Super Duper")

5 Replies

  • srinivt's avatar
    srinivt
    Microsoft Employee
    8 years ago

    CALCULATE(CALCULATE(DISTINCTCOUNT(Sales[Document_No]), FILTER(ALL(Sales[Document_No]), CALCULATE(COUNT(Sales[Line_No])) = 5), Sales[Line_Amount] = 0), Sales[Manufacturer] = "Super Duper")

     

    OR

     

    CALCULATE(COUNTROWS(FILTER(VALUES(Sales[Document_No]), CALCULATE(COUNT(Sales[Line_No])) = 5)), Sales[Line_Amount] = 0), Sales[Manufacturer] = "Super Duper")

    • legrand's avatar
      legrand
      Helper I
      8 years ago
      Hi srinivt,
      thanks you for your reply. Without having the chance to test your solution at the moment it looks like all 5 rows from „super duper“ have to be line_amount = 0 to be counted by your formulas, am I right? The formula also has to work if just one of those 5 rows has the line_amount = 0.

      The use case is an analysis of a promotion. The customer gets 5 items of the same manufacturer of which 4 are being invoiced and the 5th is free. I want to see, how often this constellation has been used. Maybe this helps explaining what I’m trying to achieve. Thanks so far!
      • srinivt's avatar
        srinivt
        Microsoft Employee
        8 years ago

        Yes the second option would have the issue not the first. The second option then would have to be tweaked slightly to account for that something like: 

         

        CALCULATE(COUNTROWS(FILTER(VALUES(Sales[Document_No]), CALCULATE(COUNT(Sales[Line_No]), ALLEXCEPT(Sales, Sales[Document_No])) = 5)), Sales[Line_Amount] = 0), Sales[Manufacturer] = "Super Duper")