Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi - I have a CRM data dump with a challenge. For every CALL_NAME there can be multiple rows with a row for each PRODUCT_NAME that was discussed. EG
CALL_NAME | PRODUCT_NAME |
ACB123 | PR1 |
ACB123 | PR2 |
ACB123 | PR3 |
CAD456 | PR2 |
CAD456 | PR7 |
I need to classify each row as valid or not. They are valid if the CALL_NAME = PR1. Therefore if CALL_NAME has PR1 listed against it somewhere, return VALID, if not NOT VALID.
CALL_NAME | PRODUCT_NAME | VALIDITY |
ACB123 | PR1 | VALID |
ACB123 | PR2 | VALID |
ACB123 | PR3 | VALID |
CAD456 | PR2 | NOT VALID |
CAD456 | PR7 | NOT VALID |
Any ideas for DAX on the 'VALIDITY' column?
Solved! Go to Solution.
Hi,
This calculated column formula works
=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CALL_NAME]=EARLIER(Data[CALL_NAME])&&Data[PRODUCT_NAME]="PR1"))>=1,"Valid","Not valid")
Hope this helps.
Hi,
This calculated column formula works
=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CALL_NAME]=EARLIER(Data[CALL_NAME])&&Data[PRODUCT_NAME]="PR1"))>=1,"Valid","Not valid")
Hope this helps.
Hi @lewdow ,
If you want to do this in the Power Query side, this is a simple Conditional Column. If you want to use DAX, then it will probably be something like this:
Validity = If(table[Product_Name] = "PR1", "Valid","Not Valid")
I can't test this right now, the PR1 might have no quotes or single quotes around it for this to work.
Proud to be a Datanaut!
Private message me for consulting or training needs.
Thanks - I need 'Valid' to appear even where PR1 isn't (but the call name that has used PR1 is)...not sure this will do that
Hey @lewdow ,
If I am understanding what you are asking - you are saying that WHENEVER a call name had a product name at any point that was a "PR1" then it is valid? If that is true, then I would probably do a filter in the Query Editor (may have to duplicate the original table) for the PR1 value and add a column for the then link that to the original table in the relationships (or add it as a column in a merge in the Query Editor).
If you really want to get a DAX way to do this and not use Query Editor then please mark this as solved and create your request in the DAX forum
Proud to be a Datanaut!
Private message me for consulting or training needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |