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
mterry
Helper V
Helper V

IF and SWITCH in measures

I don't have much experience using IF for creating a measure and just stumbled across switch today, but it appears that if I want to create a measure using either, I can only use measures that I've already created rather than a a column in my table?

 

In my specific case I have a table that has 2 different addresses for the beginning and end of a year; some of the addresses change at certain dates and some remain the same. I wanted to create a dynamic measure for the current address based on the date using IF - i.e. if the date of change is greater than today or blank then the 'current' address is the one listed in the beginning of year column, and if the date of change is less than today then the 'current' address is the one listed in the end of year column. However, I'm not able to select either of the columns when attempting to create a measure. So is my best option here to create a new column that will indicate the current address based on dates?

1 ACCEPTED SOLUTION

Hi @mterry,

 

Please try a measure like this:

Measure =
IF (
    MIN ( 'Table1'[Move Date] ) > TODAY ()
        || ISBLANK ( MIN ( 'Table1'[Move Date] ) ),
    MIN ( Table1[Address] ),
    MIN ( Table1[New Address] )
)

IF_and_SWITCH_in_measures

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
MFelix
Super User
Super User

Hi @mterry,

Like in any measure the use of columns suppose to have an aggregator to make the comparision so using IF or SWITCH in your measure with a date column you must use a MAX, MIN for example to make the selection within your column and the IF / SWITCH function work properly.

Regards,Felix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Yep, that's what smoupre said.

 

smoupre:  here's a sample of the data - the expected Current Locaiton for No. 1 would be (using TODAY() and assuming it's 2/3/2018), would be Acorn Street, for No. 2 it would be Bling Street, for No. 3 it would be High Street, and No. 4 it would be Mountain Street. Is the best option here to create a new column for the current location using the dates?

 

No.AddressNew AddressMove Date
1Acorn StreetCamp Street10/31/2018
2Doe StreetBling Street2/1/2018
3High Street1st Street8/1/2018
4Car StreetMountain Street1/1/2018

Hi @mterry,

 

Please try a measure like this:

Measure =
IF (
    MIN ( 'Table1'[Move Date] ) > TODAY ()
        || ISBLANK ( MIN ( 'Table1'[Move Date] ) ),
    MIN ( Table1[Address] ),
    MIN ( Table1[New Address] )
)

IF_and_SWITCH_in_measures

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

It looks like that worked, thank you so much! I don't (or didn't until now) have any experience with the || (or &&) operators, those look like they will be very helpful going forward, I appreciate it. 

Greg_Deckler
Super User
Super User

If you reference a column in a measure, you must use the fully qualified name (Table[Column]) and you must use an aggregator like MAX, MIN, SUM, etc.


@ 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 

Which aggregator would you use in a SWITCH TRUE() -measure where you need to find if (Calendar[Date]) is weekday or saturday/sunday?
"value1":  (Calendar[Date]) = "Weekday"; 
"result1",
"value2":  (Calendar[Date]) = "Saturday";
"result2"
"value3": (Calendar[Date]) = "Sunday";
"result 3"


I can't fit an aggregator like MIN, MAX, SUM.. It seems to me that Calculate in combination with Filter might be a solution, but I'm currently not qualified enough to achieve that.

 

The required result is:

IF (Calendar[Date] = "Weekday" then SalarisNormaal + SalarisOVW1 + SalarisOVW2
IF (Calendar[Date] = "Saturday" then SalarisZaterdag + SalarisZatOVW1 + SalarisZatOVW2
IF (Calendar[Date]  = "Sunday" then SalarisZondag + SalarisZonOVW1 + SalarisZonOVW2


Thanks in advance.

 

 

Salaris1 = 
VAR SalarisNormaal =   
    SUMX (
                'Eigen overzichten',
                'Eigen overzichten'[UrenNormaal] 
                    * RELATED ( Personeel[Uurloon] ) 
    )
VAR SalarisOVW1 =  
    SUMX (
            'Eigen overzichten',
            'Eigen overzichten'[UrenOVW1]
                * RELATED ( Personeel[Uurloon] )
                * RELATED ( Opdrachtgever[Loon_ovw1] )
    )     
VAR SalarisOVW2 = 
    SUMX (
            'Eigen overzichten',
            'Eigen overzichten'[UrenOVW2]
                * RELATED ( Personeel[Uurloon] )
                * RELATED ( Opdrachtgever[Loon_ovw2] )
    )
VAR SalarisZaterdag = 
    SUMX ( 
            'Eigen overzichten',
            'Eigen overzichten'[UrenNormaal]
                * RELATED ( Personeel[Uurloon] )
                * RELATED ( Opdrachtgever[Loon_zaterdag] )
    )
VAR SalarisZatOVW1 = 
    SUMX (
            'Eigen overzichten',
            'Eigen overzichten'[UrenOVW1]
                * RELATED ( Personeel[Uurloon] )
                * RELATED ( Opdrachtgever[Loon_ovw1] )
    )
VAR SalarisZatOVW2 =
    SUMX (
        'Eigen overzichten',
        'Eigen overzichten'[UrenOVW2]
            * RELATED ( Personeel[Uurloon] )
            * RELATED ( Opdrachtgever[Loon_ovw2] )
    )
VAR SalarisZondag = 
    SUMX ( 
        'Eigen overzichten',
        'Eigen overzichten'[UrenNormaal]
            * RELATED ( Personeel[Uurloon] )
            * RELATED ( Opdrachtgever[Loon_zondag] )
    )
VAR SalarisZonOVW1 =
    SUMX (
        'Eigen overzichten',
        'Eigen overzichten'[UrenOVW1]
            * RELATED ( Personeel[Uurloon] )
            * RELATED ( Opdrachtgever[Loon_ovw1] )
    )
VAR SalarisZonOVW2 =
    SUMX ( 
        'Eigen overzichten',
        'Eigen overzichten'[UrenOVW2]
            * RELATED ( Personeel[Uurloon] )
            * RELATED ( Opdrachtgever[Loon_ovw2] )
    )     

RETURN
    SWITCH(
        TRUE(),

 

 

 

@krampit1 Please don't highjack a solved post from over 3 years ago. Just post as a new question in the forums. Please.


@ 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...

Okay got it, so filtering through individual rows based on dates won't be an option it seems. Thanks 

Depends, you can use CALCULATE with FILTER clauses to filter down to individual rows. I'd really need to see some sample/example data and expected output to steer you in the right direction.


@ 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...

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.