Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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" )
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" )
Dear @AIB,
Thank you so much for your elaborated answer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |