Forum Discussion
Is a row valid or not?
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?
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.
4 Replies
- collinq
Super User
Hi Anonymous ,
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.
I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
- collinq
Hey Anonymous ,
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
I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
- Ashish_Mathur
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.
