Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Distinct count based on criteria from another column

Hello,

 

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

Store IDSurvey DateResult
11/1/2020Good
12/3/2020Good
22/6/2020Bad
31/10/2020Bad
32/15/2020Bad
42/22/2020Good

 

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/1271734#M557823

 

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!

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous - Perhaps:

     

    Measure =
  VAR __Table = ADDCOLUMNS('Table',"Result",[Result])
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Result]="Good"),"Store ID",[Store ID])))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Greg, but it is returning blank for me. 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.