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.
Hello, i want to use a measure in conditional formatting, but i keep getting the message that SUMMARIZECOLUMNS are not made for this context, but there is a way to create the data i want in other way?
Heatmap Maximum Value =
VAR _table = SUMMARIZECOLUMNS(dAccount[Code]
,dStudies[Year]
,"_share"
,[Value % by Account]
)
VAR _maximum = CALCULATE(MAXX(_table,[_share]), ALL(dAccount[Code]))
RETURN _maximum
This is the measure, i use this to find the maximum value in a table visualization my idea is to create a heatmap table, but the visuals i see for this not working well as i expected.
So the measure im using for get the maximum value in this visual, in this case is 55,49% but for conditional formating this not work well, so i was trying to create another measure only for this, something like:
Conditional Heatmap =
VAR _percentage = [Heatmap Maximum Value] / 5
VAR _20percent = _percentage * 1
VAR _40percent = _percentage * 2
VAR _60percent = _percentage * 3
VAR _80percent = _percentage * 4
VAR _value = [Value % by Account]
VAR _pos =
IF(_value <= _20percent, 0
,IF(_value > _20percent && _value <= _40percent, 1
,IF(_value > _40percent && _value <= _60percent, 2
,IF(_value > _60percent && _value <= _80percent, 3
,IF(_value > _80percent, 4, 0)
)
)
)
)
RETURN _pos
Someone have an idea on how to solve this? or another way to get similar results?
thanks for help 😉
Solved! Go to Solution.
// Try this measure. It uses
// ALLSELECTED to just return the
// maximum of the values across
// the visible accounts and visible
// Years. Visible in the matrix,
// of course.
[Heatmap Maximum Value] =
CALCULATE(
MAXX(
SUMMARIZE(
// FactTable is the table
// that the dims Account and
// Studies link to.
FactTable,
dAccount[Code],
dStudies[Year]
),
[Value % by Account]
),
ALLSELECTED( dAccount ),
ALLSELECTED( dStudies )
)
// Try this measure. It uses
// ALLSELECTED to just return the
// maximum of the values across
// the visible accounts and visible
// Years. Visible in the matrix,
// of course.
[Heatmap Maximum Value] =
CALCULATE(
MAXX(
SUMMARIZE(
// FactTable is the table
// that the dims Account and
// Studies link to.
FactTable,
dAccount[Code],
dStudies[Year]
),
[Value % by Account]
),
ALLSELECTED( dAccount ),
ALLSELECTED( dStudies )
)
This works pretty well thank you o/
// Once you've sorted out [Meatmap Maximum Value]
// you can simplify your other measure like this:
Conditional Heatmap =
VAR _percentage = [Heatmap Maximum Value] / 5
VAR _value = [Value % by Account]
VAR _pos =
switch( true(),
// _percentage is 20%
_value <= 1 * _percentage, (1 - 1),
_value <= 2 * _percentage, (2 - 1),
_value <= 3 * _percentage, (3 - 1),
_value <= 4 * _percentage, (4 - 1),
(5 - 1)
)
RETURN
_pos
Ahn this works pretty well, i didn't know how to use the switch haha, one question why the (1 - 1) instead 0 value?
i was trying something i seen on the web HAHA, but now i know how it works o/
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 |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |