Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear community,
Below is my measure, is there a way i can simplify this?
BDE check = IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Valencia, Gonzalo","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Webster, Julianne","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="DiBlasio, Wendy","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Maliepaard, Yolanda","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Geyer, Jody","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Croft, Ben","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Gardner, Toby","Yes-BDE",if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Valencia, Gonzalo", "Yes-PL",if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Webster, Julianne","Yes-PL",if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="DiBlasio, Wendy","Yes-PL", if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Maliepaard, Yolanda","Yes-PL", if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Geyer, Jody","Yes-PL", if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Croft, Ben","Yes-PL", if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Gardner, Toby","Yes-PL",if('Sales and Pipeline Full Data Ex'[Identified By]="Valencia, Gonzalo","Yes-Identifed By", if('Sales and Pipeline Full Data Ex'[Identified By]="Webster, Julianne","Yes-Identified By", if('Sales and Pipeline Full Data Ex'[Identified By]="DiBlasio, Wendy","Yes-Identifiedy By", if('Sales and Pipeline Full Data Ex'[Identified By]="Maliepaard, Yolanda","Yes-Identified By", if('Sales and Pipeline Full Data Ex'[Identified By]="Geyer, Jody","Yes-Identified By", if('Sales and Pipeline Full Data Ex'[Identified By]="Croft, Ben","Yes-Identified By", if('Sales and Pipeline Full Data Ex'[Identified By]="Gardner, Toby","Yes- Identified By",if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Valencia, Gonzalo","Yes-PT",if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Webster, Julianne","Yes-PT", if('Sales and Pipeline Full Data Ex'[Pursuit Team]="DiBlasio, Wendy","Yes-PT",if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Maliepaard, Yolanda","Yes-PT", if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Geyer, Jody","Yes-PT", if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Croft, Ben","Yes-PT", if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Gardner,Toby","Yes-PT","No"))))))))))))))))))))))))))))
Solved! Go to Solution.
hi, @vjnvinod
For your requirement, you could use IN Function in your formula,
and "Pursuit Team" coloumn has more than 1 names in it, you need to use SEARCH Function in it.
BDE check = IF ( 'Sales and Pipeline Full Data Ex'[Global Business Development Executive] IN { "Valencia, Gonzalo", "Webster, Julianne", "DiBlasio, Wendy", "Maliepaard, Yolanda", "Geyer, Jody", "Croft, Ben", "Gardner, Toby" }, "yes bde", IF ( 'Sales and Pipeline Full Data Ex'[Pursuit Leader] IN { "Valencia, Gonzalo", "Webster, Julianne", "DiBlasio, Wendy", "Maliepaard, Yolanda", "Geyer, Jody", "Croft, Ben", "Gardner, Toby" }, "yes pl", IF ( 'Sales and Pipeline Full Data Ex'[Identified By] IN { "Valencia, Gonzalo", "Webster, Julianne", "DiBlasio, Wendy", "Maliepaard, Yolanda", "Geyer, Jody", "Croft, Ben", "Gardner, Toby" }, "Yes-Identified By", IF ( SEARCH ( "Valencia, Gonzalo", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Webster, Julianne", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "DiBlasio, Wendy", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Maliepaard, Yolanda", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Geyer, Jody", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Croft, Ben", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Gardner, Toby", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0, "Yes-PT", "No" ) ) ) )
By the way, you could use this tool to format your dax formula.
https://www.daxformatter.com/#
Note: Please understand that this tool is provided “AS IS” with no warranties or guarantees , and confers no rights.
Best Regards,
Lin
hi, @vjnvinod
For your requirement, you could use IN Function in your formula,
and "Pursuit Team" coloumn has more than 1 names in it, you need to use SEARCH Function in it.
BDE check = IF ( 'Sales and Pipeline Full Data Ex'[Global Business Development Executive] IN { "Valencia, Gonzalo", "Webster, Julianne", "DiBlasio, Wendy", "Maliepaard, Yolanda", "Geyer, Jody", "Croft, Ben", "Gardner, Toby" }, "yes bde", IF ( 'Sales and Pipeline Full Data Ex'[Pursuit Leader] IN { "Valencia, Gonzalo", "Webster, Julianne", "DiBlasio, Wendy", "Maliepaard, Yolanda", "Geyer, Jody", "Croft, Ben", "Gardner, Toby" }, "yes pl", IF ( 'Sales and Pipeline Full Data Ex'[Identified By] IN { "Valencia, Gonzalo", "Webster, Julianne", "DiBlasio, Wendy", "Maliepaard, Yolanda", "Geyer, Jody", "Croft, Ben", "Gardner, Toby" }, "Yes-Identified By", IF ( SEARCH ( "Valencia, Gonzalo", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Webster, Julianne", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "DiBlasio, Wendy", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Maliepaard, Yolanda", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Geyer, Jody", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Croft, Ben", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0 || SEARCH ( "Gardner, Toby", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0, "Yes-PT", "No" ) ) ) )
By the way, you could use this tool to format your dax formula.
https://www.daxformatter.com/#
Note: Please understand that this tool is provided “AS IS” with no warranties or guarantees , and confers no rights.
Best Regards,
Lin
Hi, you can start to reduce using IN
Example:
IF (Table1[NameColumn] IN {"Angela", "Betty", "Karl"} , "TOP", Continue
Regards
Victor
thanks, super helpful
my new measure is below
BDE check = IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive] IN {"Valencia, Gonzalo","Webster, Julianne","DiBlasio, Wendy","Maliepaard, Yolanda","Geyer, Jody","Croft, Ben","Gardner, Toby"},"yes bde",IF('Sales and Pipeline Full Data Ex'[Pursuit Leader] IN {"Valencia, Gonzalo","Webster, Julianne","DiBlasio, Wendy","Maliepaard, Yolanda","Geyer, Jody","Croft, Ben","Gardner, Toby"},"yes pl",IF('Sales and Pipeline Full Data Ex'[Identified By] IN {"Valencia, Gonzalo","Webster, Julianne","DiBlasio, Wendy","Maliepaard, Yolanda","Geyer, Jody","Croft, Ben","Gardner, Toby"},"Yes-Identified By",IF('Sales and Pipeline Full Data Ex'[Pursuit Team] IN {"Valencia, Gonzalo","Webster, Julianne","DiBlasio, Wendy","Maliepaard, Yolanda","Geyer, Jody","Croft, Ben","Gardner, Toby"},"Yes-PT"))))
there is one more issue here
i see above measure is not reading my "Pursuit Team" coloumn
below is my pursuit team coloumn, possibly because i have more thn 1 names in the field,
is there a way we can sort this?
You have simplified your question. But to provide any suggestion, Without any background of what exactly you are trying to do with this or provide with some sample data. Noone will have a scooby doo about this massive measure.
Proud to be a PBI Community Champion
🙂
below is my 4 coloumns
and my table name is
Sales and Pipeline Full Data Ex'[Global Business Development Executive.
i want to create a new coloumn, with the below condition
1) if my coloumn
@vjnvinod Thanks for this. Is it possible to have some sample data to test on. Also, why can't you have a calculated column to derive this logic by having all these values into different list variables and then flagging them appropriately.
Proud to be a PBI Community Champion
thanks Manohar,
it was a small "IN" function to reduce the complexity.
Can you read my last thread on this topic, not sure if you can find a solution on tht?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |