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?

Hi Anonymous, 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] ) ) Best Regards,Dale

IF and SWITCH in measures | Microsoft Fabric Community

9 Replies

Greg_Deckler

Community Champion

8 years ago

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.

Anonymous
Not applicable
8 years ago
Okay got it, so filtering through individual rows based on dates won't be an option it seems. Thanks
- Greg_Deckler
  Community Champion
  8 years ago
  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.

Anonymous

Not applicable

5 years ago

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(),

Greg_Deckler
Community Champion
5 years ago
Anonymous Please don't highjack a solved post from over 3 years ago. Just post as a new question in the forums. Please.

MFelix
Super User
8 years ago
Hi Anonymous,

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
- Anonymous
  Not applicable
  8 years ago
  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. Address New Address Move Date
  1 Acorn Street Camp Street 10/31/2018
  2 Doe Street Bling Street 2/1/2018
  3 High Street 1st Street 8/1/2018
  4 Car Street Mountain Street 1/1/2018
  - v-jiascu-msft
    Microsoft Employee
    8 years ago
    Hi Anonymous,
    
    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] ) )
    
    Best Regards,
    Dale

Forum Discussion

IF and SWITCH in measures

9 Replies

Related content

Alternative to SWITCH ()?

Switch measure

Switch Measure - conditional formatting

Switch -Containsstring

Order switching mid workflow

No.	Address	New Address	Move Date
1	Acorn Street	Camp Street	10/31/2018
2	Doe Street	Bling Street	2/1/2018
3	High Street	1st Street	8/1/2018
4	Car Street	Mountain Street	1/1/2018