Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 a...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    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] )
)

    IF_and_SWITCH_in_measures

     

    Best Regards,

    Dale

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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's avatar
    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity 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's avatar
    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity 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.