Forum Discussion

AZL's avatar
AZL
Frequent Visitor
7 years ago
Solved

Count Distinct with Data Filtering

Greetings,

 

Guys, I have the folloiwng problem:

 

I have the follwoing dataset

 

 

and here is the output chart

 

As you notice for item "B" the count distinct without filtering the status is "2",wheres with status filtering it gives a total of "3"

 

what I need to do is to apply the status filter but I want to get the same total distinct number without the filter.

 

In other words, if the input data is available in Completed status i dont want to to be counted in Pending status.

 

For example in case of Area "B" in my dataset:

 

I have two dataset under Completed status which are 1 and 22 so distinct count should be 2 (which is correct)

now for pending status there's only one dataset which already avialabe in completed status. Hence I want the output to be zero.

 

 

Is there any way to do that in one chart??

Need Help

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi AZL ,

     

    Try this measure, please.

    Measure 2 =
VAR hasCompletedPending =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE (
                'Table1',
                Table1[Data],
                Table1[Status],
                "statusNum", CALCULATE ( COUNT ( Table1[Data] ), ALL ( Table1[Status] ) )
            ),
            [statusNum] >= 2
        ),
        "data", [Data]
    )
RETURN
    IF (
        MIN ( Table1[Status] ) = "Pending",
        CALCULATE (
            COUNT ( Table1[Data] ),
            FILTER ( Table1, NOT [Data] IN hasCompletedPending )
        ),
        COUNT ( Table1[Data] )
    )

    Count-Distinct-with-Data-Filtering

     

     

    Best Regards,

    • AZL's avatar
      AZL
      Frequent Visitor
      7 years ago

      Greetings,

       

      v-jiascu-msft 

       

      I tried it and i'm getting the following error:

       

      "A single value for column 'Status' in table 'table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

       

      I'm trying to understand the script but I couldn't understand why did you use ( [statusNum]>=2),"data" )

       

      I will appreciate it if you can attach a power bi file with the script for more explanation.

       

      Thank you