Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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

Greg_Deckler
Super User
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.