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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Identify and Show Duplicate Records Using Nested IF or SWITCH statements

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:
thewlett_0-1624550743809.png

 


Expected Result:
 
thewlett_0-1624551380217.png

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

thewlett_0-1624932754298.png

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

thewlett_2-1624933239824.png

 

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

thewlett_0-1624932754298.png

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

thewlett_2-1624933239824.png

 

 

Anonymous
Not applicable

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

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
Community Support
Community Support

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:

vangzhengmsft_0-1624845850776.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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