Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
AZL
Frequent Visitor

Count Distinct with Data Filtering

Greetings,

 

Guys, I have the folloiwng problem:

 

I have the follwoing dataset

 

Capture.JPG

 

and here is the output chart

 

11Capture.JPG

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??

1 ACCEPTED SOLUTION

Hi @AZL ,

 

Please download it from the attachment.

In [statusNum]>=2, "=2" means there are "Completed" and "Pending" at the same time. ">2" means there could be a third status just in case.

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi @AZL ,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft 

 

Sorry....I forgot to update it last time I logged in.

 

Thank you so much for the solution. 

Hi @AZL ,

 

Please download it from the attachment.

In [statusNum]>=2, "=2" means there are "Completed" and "Pending" at the same time. ">2" means there could be a third status just in case.

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.