Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Aggregate value from a measure

Hi all,

 

I made a calculated measure that gives me a threshold for the status of our machines: 

Threshold Audit = SWITCH(TRUE(),[DateDiff Last Audit]<=14,"Green", [DateDiff Last Audit]<=39,"Amber",[DateDiff Last Audit]>=40,"Red")
 
If i just put this measure on the canvas together with the column field "machine serial", i get a line per line indication on what the machine threshold is. However, i would like to see these values aggregated, meaning, green = X, amber = Z and red = Y, and that i cannot seem to do.
 
I am using a direct query from hana, so i dont have access to the "behind the scenes" of the data source. Can anyone help me?
Thanks!
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi Anonymous 

     

    you will have to create a separate measure for each status. I have not tested it against Hana, but it works with MS SSAS:

    Amber =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'yourTable'[MachineID] ),
        FILTER ( 'yourTable', [DateDiff Last Audit] > 14 && [DateDiff Last Audit] > 39 )
    )
)

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak,

       

      I would like the result to be displayed on a card. So, for instance, a card for Green status with the sum of all the machines that fell into green on the threshold. I currently have one calculated measure (threshold) and one column (machine serial). If i put them into the table vizualization, it works fine, but i cannot seem to get the total of machines under each of the thresholds.

       

       

      I read the articles you suggested, but they rely on a disconnected table or on grouping, and none of these options are possible to me due to the Hana connecton. Any other thoughts?

      • sturlaws's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        Hi Anonymous 

         

        you will have to create a separate measure for each status. I have not tested it against Hana, but it works with MS SSAS:

        Amber =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'yourTable'[MachineID] ),
        FILTER ( 'yourTable', [DateDiff Last Audit] > 14 && [DateDiff Last Audit] > 39 )
    )
)

         

        Cheers,
        Sturla

        If this post helps, then please consider Accepting it as the solution. Kudos are nice too.