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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
B_Real
Advocate IV
Advocate IV

if then else

Most languages have a structure that allows multiple conditions to be met in an IF statement, like this:

 

IF condition1 THEN result1 Else If condition2 THEN result2 ELSE result3.

 

Except for DAX. Using DAX you have to nest your IF statements instead, like this:

 

=IF(condition1,result1,IF(condition2,result2,result3))

 

This gets really messy when the number of conditions you require to meet increase to say 5, 10, 20 or more.

 

Can anyone explain why DAX cannot perform 'if then else' and whether it is likely to expand in the near future to be able to do so? Or are there any other tips n tricks to look at here to make it easier?

1 ACCEPTED SOLUTION
Baskar
Resident Rockstar
Resident Rockstar

Cool my dear friend.

 

We can do anything in DAX cool.

 

Try this,

 

Measure =

Switch ( True(),

 condition1,"Result1"  ,

    condition1,"Result1"  ,

etc...                     ) 

 

 

let me if any help in DAX.

 

 

View solution in original post

11 REPLIES 11
kalhassan11
New Member

Hi All, 
Please if anyone can help, this is the general logic: 

IF BU selected THEN select Row that has "All" in Grade

IF Grade selected THEN select Row that has "All" in BU

IF Nothing selected THEN Select Row that has "All" in BU and "All" in Grade 

IF Both BU and Grade Selected THEN keep rows as it is 

RETURN SUM(Value)/DISTINCTCOUNT([Date])

 

Baskar
Resident Rockstar
Resident Rockstar

Cool my dear friend.

 

We can do anything in DAX cool.

 

Try this,

 

Measure =

Switch ( True(),

 condition1,"Result1"  ,

    condition1,"Result1"  ,

etc...                     ) 

 

 

let me if any help in DAX.

 

 

Thank You !

Anonymous
Not applicable

@Baskar

 

I am trying to write this if statement in dax but i am not getting the result can you please help me out

 

Forecast Category II = IF(DSAPJ_Opportunity[Forecast Category]="Open",IF((DSAPJ_Opportunity[Forecast Category]="Pipeline" & DSAPJ_Opportunity[Sales Stage1] IN {"01","02","03","04A","04B","05","06"},"Pipeline",IF(DSAPJ_Opportunity[Forecast Category]="Upside" & DSAPJ_Opportunity[Sales Stage1] IN {"04A","04B","05"},"Upside",IF(DSAPJ_Opportunity[Forecast Category]="Commit" & DSAPJ_Opportunity[Sales Stage1] IN {"04A","04B","05"},"Commit",IF(DSAPJ_Opportunity[Forecast Category]="Won" & DSAPJ_Opportunity[Sales Stage1] = "06" , "Won","DQ Issues"))))))

Anonymous
Not applicable

I tried to use the above query but not excusted.

 

I wanted to split the condition into the three different statement.

 

  • Avg time from purchase request[PR->DATE1] to Inquiry floated to supplier(active request)[TENDER->DATE3] (days) 
  • Avg time from {Inquiry floated to supplier}[TENDER->DATE3] to {Commercial approval done [TENDER->DATE10] (days)}
  • Average time from Commercial approval done[TENDER->DATE10] to PO issue [PO->DATE4](days).
Anonymous
Not applicable

For this, I think we can use a case statement how we use in SQL

Hi Bhaskar,

 

can you help me with this query how to achive with DAX.

 

IF [repair information ] = "Repeat Repair"

THEN

IF COUNTD([VIN]) / TOTAL(COUNTD([VIN])) > 0.1
THEN round(COUNTD([VIN]) / TOTAL(COUNTD([VIN])),4) END

ELSE

IF COUNTD([Strata Claim Id]) / TOTAL(COUNTD([Strata Claim Id])) > 0.1
THEN round(COUNTD([Strata Claim Id]) / TOTAL(COUNTD([Strata Claim Id])),4) END

END

 

any quick response would be appriciated.

 

Regards,

Ram.

 

Measure =
Switch ( True(),
 condition1,"Result1"  ,
    condition1,"Result1"  ,
etc...                     ) 

 

Yaas! Great shout on Switch()! I don't think I need need the True() function here tho, the Switch() function seems to take care of everything:

 

 

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])  

 

 

Also cheers for pointing out difference between DAX and M. Here's a link that describe the difference in more detail:

 

http://stackoverflow.com/documentation/powerbi/6980/power-query-m-and-dax-whats-the-difference#t=201...

 

Essentially:

 

First you use M to query data sources, clean and load data. Then you use DAX to analyze & visualise the data in Power BI. 

So as a rule of thumb: Smiley LOL

 

if extract then M if transform then (if not aggregate then M else DAX) else DAX

Specializing in Power Query Formula Language (M)
B_Real
Advocate IV
Advocate IV

Update. 

 

'If then else' is possible in Power BI, but not using freehand DAX. It only seems to be available using menus, like so:

 

Go to Edit Queries

 

Ifthenelse.JPG

 

Then Add Column -> Conditional Column

 

Ifthenelse1.JPG

 

Et voila.... your if then else statements are available...

 

Ifthenelse2.JPG

 

I'm still not sure why you can't just type these freehand in the DAX editor to save you all that menu work.

 

 

@B_Real when editing queries you are not in the DAX environment, but in M (a.k.a. Power Query).

These are completely different: Power Query is aimed at getting and transforming data into tables, while DAX is mainly focussed on aggregating data into measures.

However, some tasks can be done in both environments and both environments reside in Power BI..

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.