cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vjnvinod Member
Member

Measure check

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Measure check

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Super User
Super User

Re: Measure check

@vjnvinod  Smiley Surprised

 

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.



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

Proud to be a Datanaut !





vjnvinod Member
Member

Re: Measure check

@PattemManohar 

 

Smiley Happy

 

below is my 4 coloumns

and my table name is

Sales and Pipeline Full Data Ex'[Global Business Development Executive.

 

 

 

 

4coloumns.PNG

 

 

i want to create a new coloumn, with  the below condition

 

1) if my coloumn

"Global Business Development Executive" contains ,
Valencia, Gonzalo
Webster, Julianne,
DiBlasio, Wendy,
Maliepaard, Yolanda,
Geyer, Jody then my new coloum should show me "Yes-BDE"
 
and if my coloumn
Pursuit Leader contains,
Webster, Julianne, then my new coloum should show me "
Yes-PL
 
and similarly for other 2 fields as well
 
the above measue works fine for me, except for coloumn Pursuit team because it has name combined something like this
 
Jones, Colin;Chen, Cecile;
 
 
Super User
Super User

Re: Measure check

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



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

Proud to be a Datanaut !





Vvelarde Super Contributor
Super Contributor

Re: Measure check

@vjnvinod 

 

Hi, you can start to reduce using IN

 

Example:

 

IF (Table1[NameColumn] IN {"Angela", "Betty", "Karl"} , "TOP",     Continue

 

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




vjnvinod Member
Member

Re: Measure check

@Vvelarde 

 

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?

 

pursuit coloumn.PNG

 

vjnvinod Member
Member

Re: Measure check

@PattemManohar 

 

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?

Community Support Team
Community Support Team

Re: Measure check

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 35 members 1,044 guests
Please welcome our newest community members: