cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcnemare
Frequent Visitor

using if function to create custom column

Hello All,

 

I work with info input by sales people, so there is alot of room for human error. To account for that in my dashboards I am trying to create a custom columns that would compare specific information namely Customer ID and branch location that we get from the sales module data to what we have recorded on our customer's info via CRM. Comparing these two tables.

 

I am looking to get a specific response, yes if it does not match. 

No if it does.

 

That way I can exclude it from our dashboards until my team goes in and fixes the data, and it will be reflected on the next scheduled refresh.

 

I am having a tough time with creating this custom IF column. Currently I have a an error stating 

Token RightParen expected

 

It identifies this on line 8, for the comma "," after  ") > 0"

 

When I delete that area, then it starts to want me to delete the logic values of my IF expression.

 

I am hoping someone could assist me and let me know what I am doing incorrectly. 

 

Best,

 

Ethan

 

 

if(
    SUMX (#"TM product enrollment master list",
    Find(
        (#"TM product enrollment master list"[Branch Description]),
        (#"Navigator Account Extract"[Branch Description])
        ,0
    )
    ) > 0,
    "No",
    "Yes"
)

 

 

 

8 REPLIES 8
amitchandak
Super User IV
Super User IV

@mcnemare , I doubt that # too

IN case you are trying in edit query mode. You need M code and this like dax.

 

Try like

if(
SUMX ("TM product enrollment master list",
Find(
("TM product enrollment master list"[Branch Description]),
("Navigator Account Extract"[Branch Description]),
,0
)
) > 0,
"No",
"Yes"
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Greg_Deckler
Super User IV
Super User IV

@mcnemare I think you want:

 

if(
    Find(
        (#"TM product enrollment master list"[Branch Description]),
        (#"Navigator Account Extract"[Branch Description]),,
        ,0
    ) > 0,
    "No",
    "Yes"
)

 

Note: The double comma is NOT a mistake!!

 

You might be interested in this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Fuzzy/m-p/1352914#M608


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Thank you for your assistance,

When using this function to create a custom column, it did resolve the previous issue. But I am getting

a token literal expected error.

thoughts?


(#"Navigator Account Extract"[Branch Description]),,

if(
    Find(
        (#"TM product enrollment master list"[Branch Description]),
        (#"Navigator Account Extract"[Branch Description]),,
        ,0
    ) > 0,
    "No",
    "Yes"
)

I was able to resolve this it was '#', using ' is better.

 

I am now getting an error stating that

 

Too many arguments were passed to the FIND function. The maximum argument count for the function is 4.

 

I'm only comparing the same 2 variables from two seperate tables.

 

Any thoughts ? @Greg_Deckler

 

if(
    Find(
        ('TM product enrollment master list'[Branch Description]),
        ('Navigator Account Extract'[Branch Description]),,
        ,0
    ) > 0,
    "No",
    "Yes"
)

---

 

Below is a simplified example of the data I am working with.

 

I am wanting to make the calulated column on the sales module table. 

 

CRM example.PNG

 

Sales Module example.PNG

 

 

@mcnemare Sorry, I missed that you had a comma in the next row, should be:

if(
    Find(
        ('TM product enrollment master list'[Branch Description]),
        ('Navigator Account Extract'[Branch Description]),
        ,0
    ) > 0,
    "No",
    "Yes"
)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




@Greg_Deckler ,

It's fine. I apprecicate all the help.

I recieved this error, I am thinking that due to the amount of variables I have are too many. Would your reccomend another DAX function or method in this case?

Error returend is:


A single value for column 'Branch Description' in table 'TM product enrollment master list' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Column = if(
    Find(
        ('TM product enrollment master list'[Branch Description]),
        ('Navigator Account Extract'[Branch Description]),
        ,0
    ) > 0,
    "No",
    "Yes"
)

@mcnemare It look like you are trying to create a measure for this. What was provided was a formula for a new column. (Hence the "Column =" at the start of the formula. If you want a measure, you need to wrap an aggregator around your column references like:

Measure = IF(
    FIND(
        MAX('TM product enrollment master list'[Branch Description]),
        MAX('Navigator Account Extract'[Branch Description]),
        ,0
    ) > 0,
    "No",
    "Yes"
)

But I am pretty sure you want a column, right?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




yes @Greg_Deckler ,

 

I am trying to create a calcualted column to ID data mimatches and to filter them out of dashboard visuals. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.