Distinct count on a filtered column - Help

[vieyepee]

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. 

[Anonymous · Accepted solution]

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.

[vieyepee]

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]

Hi @vieyepee ,

Yes, that will work too.

Thanks.

[vieyepee]

Thank you for your reply - Your solution works and i get the correct stats when the data is unfiltered. 

However when i filter it to show one month, it doesnt add up to any of my reports as per the attached screenshot. 

The number of Consultations per person remains static 

Count of persons with one consultation changes values when dates are filted but is not value im expecting 

Count of persons with more than one consultation also changesbut is not the value im expecting. 

The left side of the dashboard and in red is the DAX you provided above for the 3 measures. 

On the right are the numbers im expecting for the month of July and the matrix i built showing personID where they only had 1 consultation and the other matrix to show any person ID tht has more than 1. ( this was done using the visual filters.   i want to show the total in a card view just like how i have Total consultations for the month and total persons. 

any other suggestions? 

[Anonymous]

Hi,

Can you change the measure as below. Just update the Date field.

Number of Consulations per person = 
CALCULATE (
    COUNTROWS ( Data ),
    ALLSELECTED ( Data[PersonID], << Date_column >> ),
    Data[Service] == "Consultation"
)

Thanks.

[vieyepee]

Getting there.. Now i just need to a measure that counts all Person ID >=2 consultations, The "156" is total number of Persons.

Number of PersonID with 1 Consultation should equal 104 and personID with 2 or more should equal 52 which gives us a total of 156. 

Thank you for your help. 

[Anonymous]

Using Similar pattern to create the measures would help i guess.

Thanks.