Forum Discussion

GoRo2010's avatar
GoRo2010
New Member
3 years ago
Solved

PowerBI, if value exist then replicate

Good Day,

 

I am looking for a DAX command, as per example below, I have been able to identify duplicate email addresses ect, what I need assistance with is to say, if CustNo "1400083" as per this example have the Value underneath DuplicateCheckAmended = "DuplicateRegistered" to create a new colomb with the label "ManualUpdate" with the value "True" on all line items where CustNo is the PK (1400083).

 

 

This is my current coding to identify if a customer has registered before or not.

 

DuplicatesCheckAmend =
VAR varCurrentValue = 'Registered Customers'[CustNo]
VAR varInstances =
    COUNTROWS(
        FILTER(
            'Registered Customers',
            'Registered Customers'[CustNo] = varCurrentValue
        )
    )
var Result =
    IF(
        varInstances > 1,
        IF('Registered Customers'[Register Status] = "Registered", "DuplicateRegistered", "DuplicateNotRegistered"),
        "Unique"
    )
RETURN
    Result

 

Thank You

Need Help
  • negi007's avatar
    negi007
    3 years ago

    GoRo2010 

     

    you can create below calc column in this case

     

    ManualUpdate =
    var _tempTab = SUMMARIZE(FILTER(Tab_cust,Tab_cust[DuplicatesCheckAmend]="DuplicateRegistered"),Tab_cust[CustNo],"CountDup",COUNTx(Tab_cust,Tab_cust[DuplicatesCheckAmend]))
    var _ManualUpdate = if (CONTAINS(_tempTab,Tab_cust[CustNo],Tab_cust[CustNo]),TRUE(),FALSE())
    return
    _ManualUpdate
     
     

    pl. try this solution. also whenever you are raising any query, it is advisble to share data that can be copied. I had to convert the image to data.

     

9 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    3 years ago

    GoRo2010 in this case you can create a calc colume like below. it will count custNo in the table and if it is more than one then it will give assign true value in the new column else false

     

     

    ManualUpdate = if (CALCULATE (COUNTROWS(), ALLEXCEPT(Registered Customers, 'Registered Customers'[CustNo]))>1,TRUE(),FALSE())
    • GoRo2010's avatar
      GoRo2010
      New Member
      3 years ago

      Thank you for this negi007 , but I think I might have explained it a bit wrong.

       

      As you can see as per below example, I have 2x (sometimes even more) where the CustNo is the same, but underneath DuplicatesCheckAmend, if one of the values equals to "DuplicateRegistered" between all of the same CustNo (1400083) as per this example, a new column needs to be created where all the values against CustNo (1400083) needs to be true, there might be a case where there is multiple CustNo (9999) as an example but "DuplicateRegistered" does not exist in DuplicatesCheckAmend, those then needs to be false.

       

       

      Thank You

      • negi007's avatar
        negi007
        Icon for Community Champion rankCommunity Champion
        3 years ago

        GoRo2010 i guess DuplicateRegistered value will appear only when there are multiple values for same cust_No, in that case you can have simple calc column like below

        ManualUpdate = if(Registered_Customers[DuplicateCheckAmend]="DuplicateRegistered",TRUE(),FALSE())
         
        in case if this is not what you are looking for, then i suggest you to share sample data in the text format so that we can do testing on the data.
  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    you can also do this:

    ManualUpdate2 = var _t1='Tab_cust'[CustNo]
             var _t2 ='Tab_cust'[DuplicatesCheckAmend]
             var _result = CALCULATE(COUNTROWS(),
             FILTER(ALL('Tab_cust'),
                   'Tab_cust'[CustNo]=_t1
                   && 'Tab_cust'[DuplicatesCheckAmend]="DuplicateRegistered"))
                   RETURN
                   if(_result=1,TRUE,FALSE)