Identify and Show Duplicate Records Using Nested IF or SWITCH statements

[Anonymous]

Hello all,
I am trying to identifying duplicate parents/guardians in our school district for accessing our family portal. For privacy purposes, I have done a mock-up of the data. Essentially, I need to extrapolate this; is this parent counted more than once (means there is more than one student in our district) and if so, do they have a count (1) of Login Y and NoLogin. I need all instances of that parent record because the school column will help us connect them in the backend. We are trying to ensure that each guardian only has one login ID for all of their students. Power BI is only used to isolate these occurrences; the work to merge/connect must be done in the source system. I have created a group table to identify the duplicates (not shown in the sample data), filtering by count greater than 1 - I don't think it is working properly because I can't seem to use it to create a column with Dax. I have an example of the data and the expected result.

Raw Data:

Expected Result:

Then I can filter on ToBeShared = True, and these are the parents that need to be modified in the source system. Any help you can offer would be greatly appreciated.

Thanks!

TH

[Anonymous · Accepted solution]

I appreciate all your help. The "If" part of the statement did not like the text: I got this error message. So I used 0,1 instead.

I realize I forgot to mention a specific scenario - is it possible to have this type of result also be false?

[Anonymous]

Thank you so much for the response. I feel like this may work. However, I don't know what DAX you would use to create the_countLoginId to correspond with the parent name. My attempts to produce that measure failed. I apologize if this is simplistic DAX (I am very new). 

Cheers,

TH

[v-angzheng-msft]

Sorry for my mistake, the correct formula should be as follows

_result =
VAR _countId =
    CALCULATE (
        DISTINCTCOUNT ( Data[Login] ),
        ALLEXCEPT ( 'Data', Data[Parent name] )
    )
VAR _if =
    IF ( _countId = 1, "FALSE", "True" )
RETURN
    _if

 The other formula is just to explain the intermediate process, its formula is as follows

_countLoginId =
CALCULATE (
    DISTINCTCOUNT ( Data[Login] ),
    ALLEXCEPT ( 'Data', Data[Parent name] )
)

 Thank you for your correction.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft]

Hi, @Anonymous 

Do you mean to get the above ToBeShared result based on the login ID?
If it is, please try to create a measure like below:

_result =
VAR countId =
    CALCULATE (
        DISTINCTCOUNT ( Data[Login] ),
        ALLEXCEPT ( 'Data', Data[Parent name] )
    )
VAR _if =
    IF ( [_countLoginId] = 1, "FALSE", "True" )
RETURN
    _if

Result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.