Data changing when adding legend

[Sohan]

When I add a legend to my data, the data changes and I have no idea why this happens. Please find the PBIX file here

I'd greatly appreciate it if someone could tell me what I'm doing wrong. It's regarding the second tab in the file.

[MFelix]

Hi @Sohan ,

Your data is not changing you are changing the context of your data so that is why you are getting different results.

In your case you are making the distintc count of patient Number when you have the calculation at the week level a single patient is consider only once even if he appears in more than one Gebruiker.

However when you add the Gebruiker the context changes and you add a different level of details so now you are picking up the same patient number once for each Gebruiker.

Let's look at the example below:

If you select the patient 729-8773 on week 3 you have a single patient, however it is consider in 3 different Gebruiker so when you add the legend you get a total of 3 patients:

[Sohan]

@MFelix Thank you for your answer! It's much appreciated. Do you have any idea how I could resolve this? So the duplicates don't get shown when adding 'Gebruiker' to the legend?

[MFelix]

Hi @Sohan ,

Do you want to have the details but the total line label still be showned has you don't have the gebruiker? Or do you want the totals in gebruiker also to only add 19?

[Sohan]

I want to totals without Gebruiker as legend to be equal to the totals with Gebruiker as legend. 

[v-kkf-msft]

Hi @Sohan ,

If you want the same calculation result between legend and without legend, how do you want to deal with the situation where there are 2 or more Gebruikers with the same Patiëntnummer?

For example, Gebruikers with Patiëntnummer 272-9237 have "M. de Peijper" and "S. Rambaran". The result is 1 without the legend, and 2 when the legend is added. If you want the same result, then you need to keep only one Gebruiker per Patiëntnummer. How do you want to keep the Gebruiker?

The following is my test, set to keep the Patiëntnummer for Gebruiker with higher name sorting.  

MAX_Gebruiker = 
CALCULATE(
    MAXX(
        ALLSELECTED('CALC NP''s (Uniek)'),
        'CALC NP''s (Uniek)'[Gebruiker]
    ),
    GROUPBY(
        'CALC NP''s (Uniek)',
        Datum[Week],
        'CALC NP''s (Uniek)'[Patiëntnummer]
    )
)

Count_Patiëntnummer = 
CALCULATE(
    DISTINCTCOUNT('CALC NP''s (Uniek)'[Patiëntnummer]),
    FILTER(
        ALLSELECTED('CALC NP''s (Uniek)'[Patiëntnummer]),
        [MAX_Gebruiker] = MAX('CALC NP''s (Uniek)'[Gebruiker])
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Sohan]

@v-kkf-msft Thank you so much for your response! Instead of taking the max_name, I'd like to take the person who was first booked. See example below:

In this example (column three is date booked), I'd want 'M. de Peijper' to be taken as that individual was booked first. How can I achieve that, instead of choosing the person with the max_name?

FYI, the above is a screenshot from table 'CALC NP's (Uniek)'. Please see the link for the updated file: PBIX file 

[v-kkf-msft · Accepted solution]

Hi @Sohan ,

Try the following formula:

Earliest_book = 
CALCULATE(
    MINX(
        ALLSELECTED('CALC NP''s (Uniek)'),
        'CALC NP''s (Uniek)'[Datum boeking]
    ),
    GROUPBY(
        'CALC NP''s (Uniek)',
        Datum[Week],
        'CALC NP''s (Uniek)'[Patiëntnummer]
    )
)

Count_Patiëntnummer = 
CALCULATE(
    DISTINCTCOUNT('CALC NP''s (Uniek)'[Patiëntnummer]),
    FILTER(
        ALLSELECTED('CALC NP''s (Uniek)'[Datum boeking]),
        'CALC NP''s (Uniek)'[Datum boeking] = [Earliest_book]
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Sohan]

@v-kkf-msft Thank you so much! You have been a great help. Really appreciate it!