Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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:

 


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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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?

     

     

4 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      v-angzheng-msft
      Community Support
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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?