Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Distinct count on a filtered column - Help

Hi 

 

Currently i have raw data in table 1 as follows; I want to create table 2, to show distinct patrons who have had consultations done and the total amount. 

My end goal is to use the card visual to display number of persons that has had 1 consultation and another card to show 2 or more. 

I can do this using the matrix visual without any complications but want to show this as a card visual. 

 

thanks. 

 

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi,

    Check if this helps. Create 3 measures as below:

    Number of Consulations per person = 
CALCULATE (
    COUNTROWS ( Data ),
    ALLEXCEPT ( Data, Data[PersonID] ),
    Data[Service] == "Consultation"
)
    Count of persons with one consultation = CALCULATE( 
    DISTINCTCOUNT( Data[PersonID] ),
    FILTER( Data, [Number of Consulations per person] == 1 )
)
    Count of persons with more than one consultation = CALCULATE( 
    DISTINCTCOUNT( Data[PersonID] ),
    FILTER( Data, [Number of Consulations per person] > 1 )
)

    This is what i got with your data.

    Thanks.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

    Check if this helps. Create 3 measures as below:

    Number of Consulations per person = 
CALCULATE (
    COUNTROWS ( Data ),
    ALLEXCEPT ( Data, Data[PersonID] ),
    Data[Service] == "Consultation"
)
    Count of persons with one consultation = CALCULATE( 
    DISTINCTCOUNT( Data[PersonID] ),
    FILTER( Data, [Number of Consulations per person] == 1 )
)
    Count of persons with more than one consultation = CALCULATE( 
    DISTINCTCOUNT( Data[PersonID] ),
    FILTER( Data, [Number of Consulations per person] > 1 )
)

    This is what i got with your data.

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for the reply. 

       

      If i wanted to filter dates on the dashboard - could the stats update based on the dates chosen? This is assuming Table 1 has a date column next the Service column.

       

      How would i go about this? 

       

      Sorry new to Power BI 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        Yes, that will work too.

        Thanks.