Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Friends,
Need your advise if I can use SWITCH(TRUE() and || function like below:
In power pivot I have 2 columns named as
[X3<=AA], [AF>(AA+2)]. Then column [X3<=AA] has following values "NoAACarr","NoX3Carr", "DET=Y" and column [AF>(AA+2)] has following values "NoAFCarr", "DET=Y"
I want to first check if all the three columns has one these values in their field "NoAACarr","NoX3Carr","NoAFCarr", I apply OR (||) function if not then filter the 2 columns where it has "DET=Y".
is the below correct DAX formula
DET(X3_OnTime):=SWITCH(TRUE(),
[X3<=AA]="NoAACarr" || [X3<=AA]="NoX3Carr" || [AF>(AA+2)]="NoAFCarr" ,"NoEvent",
[X3<=AA] = "DET=Y" && [AF>(AA+2)]="DET=Y","DET(X3_OnTime)=Y","DET=N")
Once i work up the formula data looks like this:
Best Regards
Solved! Go to Solution.
Hi @anands06,
You can try to use this one, I add condition to recogined the "DEF=N" flag:
DET(X3_OnTime) =
IF (
[X3<=AA] <> "DET=Y"
|| [AF>(AA+2)] <> "DET=Y",
"NoEvent",
IF ( [X3<=AA] = [AF>(AA+2)], "DET(X3_OnTime)=Y", "DET=N" )
)
Comment:
First condition check 'No Events' flag, if not match it mean at least one field equal to "DEF=Y".
The second condition check if two fields have same value, since the default condition is at least one field equal to "DEF=Y", so it will trigger if two columns equal to "DEF=Y". If both previous two conditions all not matched, the formula will return "DEF=N".
Regards,
Xiaoxin Sheng
Hello Xiaoxin Sheng,
Thanks for taking out the time and giving valuable advise.
It works well however I also wanted to add another condition of "DET=N"
As per your formula we may miss "DET=N".
regards
Anand
Friends,
I worked and validated the data and above formula works well.
Hi @anands06,
I checked you formula and condition and find they can be simplify to following expressions, you can try it if hleps:
DET(X3_OnTime) =
IF (
[X3<=AA] <> "DET=Y"
|| [AF>(AA+2)] <> "DET=Y",
"NoEvent",
"DET(X3_OnTime)=Y"
)
Comment:
If statement to get reverse of the first condition to check if these two column not equal to "DET=Y" with OR logic. If they not match sceniaros means both two column equal to "DET=Y" and they match wiht your second condition.
Regards,
Xiaoxin Sheng
Hello Xiaoxin Sheng,
Thanks for taking out the time and giving valuable advise.
It works well however I also wanted to add another condition of "DET=N"
As per your formula we may miss "DET=N". I hope you agree with my response.
regards
Anand
Hi @anands06,
You can try to use this one, I add condition to recogined the "DEF=N" flag:
DET(X3_OnTime) =
IF (
[X3<=AA] <> "DET=Y"
|| [AF>(AA+2)] <> "DET=Y",
"NoEvent",
IF ( [X3<=AA] = [AF>(AA+2)], "DET(X3_OnTime)=Y", "DET=N" )
)
Comment:
First condition check 'No Events' flag, if not match it mean at least one field equal to "DEF=Y".
The second condition check if two fields have same value, since the default condition is at least one field equal to "DEF=Y", so it will trigger if two columns equal to "DEF=Y". If both previous two conditions all not matched, the formula will return "DEF=N".
Regards,
Xiaoxin Sheng
Hello Xiaoxin,
Thanks for your attention. It works perfectly fine.
Both IF and my SWITCH(TRUE() formula show the same result.
Good interaction with you.
best regards
Anand
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
6 |