Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning all,
I am creating a measure and cannot put this in Power Query because it is looking at other tables.
TextField1 !MergedTraining'[GAP-SH-SA-Primary.GAP SH/SA 1st contains text (an expected cell is SA)
NumberField2 !MergedTraining'[GAP √ SA] contains numbers (an expect response cell be 1)
TextField3 !MergedTraining'[OPER Pos] contains text (an expected response is OPER-SH-SA)
Basically (in my head of not-right-thinking), I want to create an If then statement, but I am running into issues where Power BI is throwing an error that says that I can't use text and numbers together.
Let's imagine that they are all in the same table "CHECK" for our purposes here. I will change that later.
If TextField"1"=SA & NumberField2="1" & TextField3="OPER-SH-SA", then output "Yes", If not "No"
I attempted this and it was successful:
Comply-SSC-SA = IF( FIRSTNONBLANK('!MergedTraining'[GAP-SH-SA-Primary.GAP SH/SA 1st],1)="SA", "YES", "NO")
I attempted to add my next field and failed with the following message: DAX comparison operations do not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
Comply-SSC-SA = IF( FIRSTNONBLANK('!MergedTraining'[GAP-SH-SA-Primary.GAP SH/SA 1st],1)="SA" & SUM('!MergedTraining'[GAP √ SA]) = 1, "YES", "NO")
I think that if I can figure out TextField1 and NumberField2, I can deal with TextField3 after that (🤞)
Any thoughts for this newbie?
Thanks.
Jamie
@jamiers I *think* you want this instead:
Comply-SSC-SA = IF( FIRSTNONBLANK('!MergedTraining'[GAP-SH-SA-Primary.GAP SH/SA 1st],1)="SA" && SUM('!MergedTraining'[GAP √ SA]) = 1, "YES", "NO")
& is the concatenation operator, && is a logical AND
Completely screwed that one up... Thanks @Greg_Deckler ...
Unfortunately, results aren't coming back as expected with it working. Is it because of the FIRSTNONBLANK that I stuck into this? Should I be searching for the "SA" in a different way?
Basically, I want this if/then statement to see that there is a SA in column 1 and only a "1" in column 2. If both are true, YES posts, if not then NO posts. Right now everything is posting as "NO"
@jamiers Might be, hard to say without sample data and such. But, I tend just to use MAX or MIN aggregator versus something like FIRSTNONBLANK. Depends on your scenario of course but I default to the simplest DAX function possible for the job and in a LOT of cases MAX and MIN do the job.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |