Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Matrix formatting

I want to highlight each row with its lowest and highest values in my matrix , I am using a measure currently to conditional format and its not working well,

MinMax1 =

VAR Vals =
CALCULATETABLE(
ADDCOLUMNS (
SUMMARIZE ( HoldTime, HoldTime[IRC],HoldTime[Team],HoldTime[CallInitiatedTime - Hour]),
"@hold", [Hold Duration (MM:SS)]
),
ALLSELECTED())
VAR MinValue = MINX ( Vals, [@hold] )
VAR CurrentValue = [Hold Duration (MM:SS)]

VAR MaxValue = MAXX ( Vals, calculate([Hold Duration (MM:SS)],filter(Vals,Vals=CurrentValue)))
VAR Result =
SWITCH (
TRUE,
CurrentValue = MinValue, 1, -- 1 for MIN
CurrentValue = MaxValue, 2 -- 2 for MAX
)
RETURN
Result
need help on how to tweak the measure
 
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , try like

     

    MinMax1 =

    VAR Vals =
    CALCULATETABLE(
    ADDCOLUMNS (
    SUMMARIZE (allselected( HoldTime), HoldTime[IRC],HoldTime[Team],HoldTime[CallInitiatedTime - Hour]),
    "@hold", [Hold Duration (MM:SS)]
    ),
    ALLSELECTED())
    VAR MinValue = MINX ( Vals, [@hold] )
    VAR CurrentValue = [Hold Duration (MM:SS)]

    VAR MaxValue = MAXX ( Vals, calculate([Hold Duration (MM:SS)],filter(Vals,Vals=CurrentValue)))
    VAR Result =
    SWITCH (
    TRUE,
    CurrentValue = MinValue, 1, -- 1 for MIN
    CurrentValue = MaxValue, 2 -- 2 for MAX
    )
    RETURN
    Result

     

     

    or

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for your response, I just did a workaround and struck here,

       

      HoldTimeCompare = var _17 = if(HoldTime[Hold Duration (MM:SS)]<blank(),CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=17)))
      var _18 = CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=18))
      var _19 = CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=19))
      var _20 = CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=20))
      var _21 = CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=21))
      var _22 = CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=22))
      var _23 = CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=23))
      var _0 = CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=0))
      var _1 = CALCULATE(HoldTime[Hold Duration (MM:SS)],filter(HoldTime,HoldTime[CallInitiatedTime - Hour]=1))
      VAR __list = { _17,_18,_19,_20,_21,_22,_23,_0,_1 }
      var maxlist =
      CALCULATE(MINX( __list,[value] ),ALLEXCEPT(HoldTime,HoldTime[IRC],HoldTime[Team],HoldTime[CallInitiatedTime - Hour]))

      var Maxi = if(maxlist=CALCULATE(minx(HoldTime,HoldTime[Hold Duration (MM:SS)]),ALLEXCEPT(HoldTime,HoldTime[Team],HoldTime[IRC],HoldTime[CallInitiatedTime - Hour])),1,0)


      return Maxi
      now for the maxi variable, i want to return only the top 1
      How can I achieve this?