Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sohan
Helper III
Helper III

Data changing when adding legend

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.

1 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]
    )
)

 image.png

 

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.

View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

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:

MFelix_0-1627377214827.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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?

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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?

 

image.png

 

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])
    )
)

image.png

 

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.

 

@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:

Sohan_0-1627631283763.png

 

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 

 

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]
    )
)

 image.png

 

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.

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.