Forum Discussion

setis's avatar
setis
Post Partisan
7 years ago
Solved

Measure vs. calculated column

Hi All, 

 

Despite making an effort in trying to understand when to use a calculated column and when a measure, I findt it difficult sometimes. 

 

I've made a calculated column to segment amounts in 7 groups and it works fine: 

 

GroupValues2 = 
SWITCH (
    TRUE ();
    Financial[Amount Paid] >0
    && Financial[Amount Paid] <=5000;"a.0-5.000";
        Financial[Amount Paid] >5000
    && Financial[Amount Paid] <=10000;"b.5.000-10.000";
        Financial[Amount Paid] >10000
    && Financial[Amount Paid] <=30000;"c.10.000-30.000";
        Financial[Amount Paid] >30000
    && Financial[Amount Paid] <=50000;"d.30.000-50.000";       
    Financial[Amount Paid] >50000
    && Financial[Amount Paid] <=100000;"e.50.000-100.000";
    Financial[Amount Paid] <=0;"f.Adjustments";
    Financial[Amount Paid] >100000;"f.>100.000"
)

I was wondering if it was possible to obtain the same results with a measure but the same code doesn't work. 

 

Could somebody please explain me why this doesn't work as a measure and if this kind of segmentation if possible with a measure?

 

Thanks in advance

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi setis

     

    Determining segments like this is usually more useful with a calculated column as you have it but you can certainly do it with a measure as well. Set Financial[Amount Paid] in the rows of a matrix visual the measure to be placed in values of the visual would be as below. Note that all we've done is substitute SELECTEDVALUE(Financial[Amount Paid]) for Financial[Amount Paid]).  In a measure there is no row context and therefore the reference to the naked column Financial[Amount Paid] makes no sense. You need to make use of the filter context in the current row of the visual to get the desired value through SELECTEDVALUE( ) 

     

    GroupValues2_Measure = 
SWITCH (
    TRUE ();
    SELECTEDVALUE(Financial[Amount Paid]) >0
    && SELECTEDVALUE(Financial[Amount Paid]) <=5000;"a.0-5.000";
        SELECTEDVALUE(Financial[Amount Paid]) >5000
    && SELECTEDVALUE(Financial[Amount Paid]) <=10000;"b.5.000-10.000";
        SELECTEDVALUE(Financial[Amount Paid]) >10000
    && SELECTEDVALUE(Financial[Amount Paid]) <=30000;"c.10.000-30.000";
        SELECTEDVALUE(Financial[Amount Paid]) >30000
    && SELECTEDVALUE(Financial[Amount Paid]) <=50000;"d.30.000-50.000";       
    SELECTEDVALUE(Financial[Amount Paid]) >50000
    && SELECTEDVALUE(Financial[Amount Paid]) <=100000;"e.50.000-100.000";
    SELECTEDVALUE(Financial[Amount Paid]) <=0;"f.Adjustments";
    SELECTEDVALUE(Financial[Amount Paid]) >100000;"f.>100.000"
)

    You can also make use of variables to make the code a bit more readable and maintainable, and execute SELECTEDVALUE only once:

     

    GroupValues2_Measure_V2 =
VAR _CurrentRowAmount =
    SELECTEDVALUE ( Financial[Amount Paid] )
RETURN
    SWITCH (
        TRUE ();
        _CurrentRowAmount > 0
            && _CurrentRowAmount <= 5000; "a.0-5.000";
        _CurrentRowAmount > 5000
            && _CurrentRowAmount <= 10000; "b.5.000-10.000";
        _CurrentRowAmount > 10000
            && _CurrentRowAmount <= 30000; "c.10.000-30.000";
        _CurrentRowAmount > 30000
            && _CurrentRowAmount <= 50000; "d.30.000-50.000";
        _CurrentRowAmount > 50000
            && _CurrentRowAmount <= 100000; "e.50.000-100.000";
        _CurrentRowAmount <= 0; "f.Adjustments";
        _CurrentRowAmount > 100000; "f.>100.000"
    )

     

     

     

     

       

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi setis

     

    Determining segments like this is usually more useful with a calculated column as you have it but you can certainly do it with a measure as well. Set Financial[Amount Paid] in the rows of a matrix visual the measure to be placed in values of the visual would be as below. Note that all we've done is substitute SELECTEDVALUE(Financial[Amount Paid]) for Financial[Amount Paid]).  In a measure there is no row context and therefore the reference to the naked column Financial[Amount Paid] makes no sense. You need to make use of the filter context in the current row of the visual to get the desired value through SELECTEDVALUE( ) 

     

    GroupValues2_Measure = 
SWITCH (
    TRUE ();
    SELECTEDVALUE(Financial[Amount Paid]) >0
    && SELECTEDVALUE(Financial[Amount Paid]) <=5000;"a.0-5.000";
        SELECTEDVALUE(Financial[Amount Paid]) >5000
    && SELECTEDVALUE(Financial[Amount Paid]) <=10000;"b.5.000-10.000";
        SELECTEDVALUE(Financial[Amount Paid]) >10000
    && SELECTEDVALUE(Financial[Amount Paid]) <=30000;"c.10.000-30.000";
        SELECTEDVALUE(Financial[Amount Paid]) >30000
    && SELECTEDVALUE(Financial[Amount Paid]) <=50000;"d.30.000-50.000";       
    SELECTEDVALUE(Financial[Amount Paid]) >50000
    && SELECTEDVALUE(Financial[Amount Paid]) <=100000;"e.50.000-100.000";
    SELECTEDVALUE(Financial[Amount Paid]) <=0;"f.Adjustments";
    SELECTEDVALUE(Financial[Amount Paid]) >100000;"f.>100.000"
)

    You can also make use of variables to make the code a bit more readable and maintainable, and execute SELECTEDVALUE only once:

     

    GroupValues2_Measure_V2 =
VAR _CurrentRowAmount =
    SELECTEDVALUE ( Financial[Amount Paid] )
RETURN
    SWITCH (
        TRUE ();
        _CurrentRowAmount > 0
            && _CurrentRowAmount <= 5000; "a.0-5.000";
        _CurrentRowAmount > 5000
            && _CurrentRowAmount <= 10000; "b.5.000-10.000";
        _CurrentRowAmount > 10000
            && _CurrentRowAmount <= 30000; "c.10.000-30.000";
        _CurrentRowAmount > 30000
            && _CurrentRowAmount <= 50000; "d.30.000-50.000";
        _CurrentRowAmount > 50000
            && _CurrentRowAmount <= 100000; "e.50.000-100.000";
        _CurrentRowAmount <= 0; "f.Adjustments";
        _CurrentRowAmount > 100000; "f.>100.000"
    )

     

     

     

     

       

    • setis's avatar
      setis
      Post Partisan
      7 years ago

      Dear @AIB,

       

      Thank you so much for your elaborated answer.