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
GoRo2010
New Member

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).

 

2022-12-23_11-37-31.png

 

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

1 ACCEPTED SOLUTION
negi007
Community Champion
Community Champion

@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
 
 
negi007_0-1671882348889.png

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.

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

9 REPLIES 9
Ahmedx
Super User
Super User

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)
negi007
Community Champion
Community Champion

@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())



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

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.

 

2022-12-23_11-37-31v2.png

 

Thank You

negi007
Community Champion
Community Champion

@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.



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

@negi007 , here is an example of what I am trying to replicate, because CustNo 1400083 has the value of "DuplicateRegistered" all values in ManualUpdate needs to be true, as per example, CustNo 999999 does not have that value in DuplicatesCheckAmended thus all CustNo 999999  needs to be false, I hope this explains it better.

 

Thank You

 

2022-12-23_11-37-31v3.png

negi007
Community Champion
Community Champion

@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
 
 
negi007_0-1671882348889.png

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.

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

@negi007 , you are a legend. Thank you for this, this has resolved my query. Have a blessed year and a merry xmas!

negi007
Community Champion
Community Champion

@GoRo2010 thanks. and cheers for new year!!!




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

@negi007 , I need this replicated with true across all CustNo where CustNo = CustNo.

 

I will create a demo excel sheet now for you. Tnx

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.