Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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??
Solved! Go to 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,
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] ) )
Best Regards,
Greetings,
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,
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,
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
83 | |
67 | |
62 | |
46 | |
45 |