Aggregate value from a measure

[Anonymous]

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!

[amitchandak]

@Anonymous , not very clear. Ate you try to put a measure on Axis ?

You have to check for binning

SEGMENTATION

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

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 · Accepted solution]

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.