Distinct count total is correct, but the columns are not

[PleaseHelpMe123]

Hi,

I am new to power BI and need some help.

I have a list of patient IDs and I want to get the total distinct patients per fiscal year broken down by fiscal period, but when I put it in a table with columns representing the periods, it gives me the distinct total per period not per year, which is not what I want. Only the grand total in the table is correct.

e.g. Here is what I'm getting for 2023 (using a slicer):

Here's what I want:

Again, to clarify, each patient should only be counted once per year, and should only be counted in the first period they appear.

In my dates table, I have per row every date from 2022-2025 (Jour"), and the corresponding fiscal period (P#) and fiscal year (Annee 2). 

In my patient data table, the relevent columns would be the patient ID and appointment date (startdatetime). 

I have done a lot of research and none of what I've read is giving me the correct results. I can acheive the results when working with imported data, but I need to keep it as direct query so my options are more limited.

I would appreciate any help you can give!

[tamerj1]

Hi @PleaseHelpMe123 
Please try

DCOUNT =
VAR T1 =
    ADDCOLUMNS (
        SUMMARIZE ( TargetTable, TargetTable[Patient_Id], Dates[Period] ),
        "FirstPeriod",
            CALCULATE (
                MINX ( SUMMARIZE ( TargetTable, Dates[Period] ), Dates[Period] ),
                ALL ( Dates )
            )
    )
VAR T2 =
    FILTER ( T1, Dates[Period] = [FirstPeriod] )
VAR Result =
    COUNTROWS ( T2 )
RETURN
    Result

[PleaseHelpMe123]

@tamerj1 

The code seems to work, but can you explain why the result is so much lower than the expected amount which was done in import mode and validated against the actual data.

2023 result:

2023 expected:

[DataNinja777]

@PleaseHelpMe123 ,

You can achieve your required output by creating in your fact table TargetTable (All years), a calculated column to identify the first time the patient visited your clinic.  

First Visit Date =
CALCULATE (
    MIN ( 'TargetTable (All years)'[Visit date] ),
    ALLEXCEPT ( 'TargetTable (All years)', 'TargetTable (All years)'[Patient_Id] )
)

(Before creating the calculated column above in your fact table, please create the relationship between your fact table date column and your data table.)

Then, you can write a measure like below to distinctcount the patients when they first visited your clinic.  

DistinctPatientCountFirstVisit =
CALCULATE (
    DISTINCTCOUNT ( 'TargetTable (All years)'[Patient_Id] ),
    'TargetTable (All years)'[Visit date] = 'TargetTable (All years)'[First Visit Date]
)

The resultant output using a simplified dummy table is as shown below, where the 2nd column shows only the distinctcount of the patients when they first visited your clinic, and the 1st column shows the normal distinctcount where the same patients are counted multiple times if revisited in different months. 

I attach an example pbix file for your reference. 

Best regards,

[PleaseHelpMe123]

@DataNinja777 

Thank you so much for your response.  You've definitely helped me think about this in the right way.

I created a measure for the first visit date, then another one for the distinct patient count.  I put in a filter because i was getting an error about a placeholder instead of a true/false.

DistinctPatientCountFirstVisit =

CALCULATE (DISTINCTCOUNT('TargetTable (All years)'[Patient_Id]),

    FILTER('TargetTable (All years)','TargetTable (All years)'[StartDateTime]='TargetTable (All years)'[First Visit Date]))

This is the table that results when plug in this measure to my table.

Its quite different from what I expected.

The biggest issue though, is that the count is not restarting at the start of the next fiscal year (column Annee 2). How should I fix this?

[PleaseHelpMe123]

Hi @DataNinja777 

I tried your adjusted suggestion, but its' giving me an error. How do I fix it?

[DataNinja777]

Hi @PleaseHelpMe123 ,

Did you create a calculated column first before using it in a measure like below?  

Best regards,

[PleaseHelpMe123]

@DataNinja777 

No,  I had tried putting it as a measure because I get this message when I try to create a column:

[DataNinja777 · Accepted solution]

Hi @PleaseHelpMe123 ,

Thank you for alerting me to the fact that there is such a limitation in Direct Query mode. I've fixed the measure as follows, referencing in it the [Year] calculated column in your fact table,

'TargetTable (All years)'.

FirstVisitDateInYear = 
CALCULATE(
    MIN('TargetTable (All years)'[Visit date]),
    ALLEXCEPT(
        'TargetTable (All years)',
        'TargetTable (All years)'[Patient_Id],
        ('TargetTable (All years)'[Year]
    )
))

Then you can reference the measure above in the final output measure below:

DistinctPatientCountFirstVisit = 
CALCULATE(
    DISTINCTCOUNT('TargetTable (All years)'[Patient_Id]),
    FILTER(
        'TargetTable (All years)',
        'TargetTable (All years)'[Visit date] = [FirstVisitDateInYear]
    )
)

I attach a modified pbix file for the Direct Query mode.  

The resultant output is as shown below:

You can verify below that the patient ID count is properly restarting in a different year.  

Best regards,