cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thewlett
Frequent Visitor

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
thewlett
Frequent Visitor

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
thewlett
Frequent Visitor

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

thewlett
Frequent Visitor

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, @thewlett 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors