Distinct count based on criteria from another column

[Anonymous]

Hello,

My data table looks like the below. "Result" is a measure that I created from other measures/fields. 

Store ID	Survey Date	Result
1	1/1/2020	Good
1	2/3/2020	Good
2	2/6/2020	Bad
3	1/10/2020	Bad
3	2/15/2020	Bad
4	2/22/2020	Good

What I want is to create a distinct count that shows me how many stores have "Good" as a result. In this example, it should show 2, because store #1 & #3 are the only ones. 

I have tried several solutions posted, including the ones found here

https://community.powerbi.com/t5/Desktop/DISTINCTCOUNT-with-filters/td-p/410253

and 

https://community.powerbi.com/t5/Desktop/Distinct-count-based-on-a-criteria-from-another-column/m-p/...

Good_result=
CALCULATE (
DISTINCTCOUNT ( Table[store ID] ),
FILTER(Table,Table[Result] = "Good"
))

But I am not sure why Power BI keeps on returning zero on me. Any help would be greatly appreciated!

[v-zhenbw-msft]

Hi @Anonymous ,

We create a sample, and the result is correct.

Your issue may be caused by Result Measure.

You can try to put the calculation conditions in the Result measure into the Filter of Good_Result.

If it doesn’t meet your requirement, could you please provide the detail of the Result Measure.

Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables. Do you want to show the result in a table visual or a card visual?

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[amitchandak]

@Anonymous , Try a new measure like

countc(filter(summarize(Table, Table[Store ID], "_1", count(Table[Store ID]), "_2", calculate(count(Table[Store ID]),Table[Result] ="Good")),[_1]=[_2]),[Store ID])

[Anonymous]

Thanks, but what is countc? If it was count, this measure doesn't seem to work. 

[Greg_Deckler]

@Anonymous - Perhaps:

Measure =
  VAR __Table = ADDCOLUMNS('Table',"Result",[Result])
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Result]="Good"),"Store ID",[Store ID])))

[Anonymous]

Thanks Greg, but it is returning blank for me.