How to create a virtual column of a virtual table to use in metrics

Anonymous — Mon, 23 Dec 2019 14:41:06 GMT

Hello everyone,
I'm new to power bi, I have a beginner problem and I ask for your help.
I have a table with disk information collection. This collection occurs several times during the day.
I want to create a metric with the sum of only the last values of the last records of each disk. You will need the following sampletime, samplevalue, and target columns.

I created a calculated column for ease of filtering because I need to filter disk names and use only what is TRUE from this column C_ContainsStringTarget = if ([M_TargetFilter]; TRUE; FALSE).

Here's M_TargetFilter = (CONTAINSSTRING (SELECTEDVALUE (V_QOS_DISK_CAPACITY_GBYTES [target]); "VOL_") ||
    CONTAINSSTRING (SELECTEDVALUE (V_QOS_DISK_CAPACITY_GBYTES [target]); "_ RDMs") ||
    CONTAINSSTRING (SELECTEDVALUE (V_QOS_DISK_CAPACITY_GBYTES [target]); "HYPERSWAP_")) &&
    (NOT (CONTAINSSTRING (SELECTEDVALUE (V_QOS_DISK_CAPACITY_GBYTES [target]); "local")))

To return the most current value I created the following metric M_LastValue =
VAR MaxData = MAX (V_QOS_DISK_CAPACITY_GBYTES [sampletime])
RETURN
Calculate (
    max (V_QOS_DISK_CAPACITY_GBYTES [samplevalue]);
    filter (V_QOS_DISK_CAPACITY_GBYTES; V_QOS_DISK_CAPACITY_GBYTES [sampletime] = MaxData
    )
)

For the purpose of target-oriented matrix visualization, adding the samplevalue column, adding the M_LastValue metric, and filtering only on the true values of the C_ContainsStringTarget column, I have the matrix with the values I need. But I don't know how to make just one metric by just summing these values returned in this view. Can someone help me?

Detail, I'm making a direct connection to a SQL server (Direct query), I think it limits the use of some functions.

How can I merge these filters into one metric and return the sum of the last recorded values only for the filtered disks with the value TRUE in the C_ContainsStringTarget column?

Thank you in advance.

Re: How to create a virtual column of a virtual table to use in metrics

Anonymous — Tue, 24 Dec 2019 10:09:09 GMT

Following solution:

M_SUM_LastValue =

sumx(

CALCULATETABLE(

all(V_QOS_DISK_CAPACITY_GBYTES);

filter(V_QOS_DISK_CAPACITY_GBYTES;

V_QOS_DISK_CAPACITY_GBYTES[samplevalue]=max(V_QOS_DISK_CAPACITY_GBYTES[samplevalue]) )

);

if(V_QOS_DISK_CAPACITY_GBYTES[sampletime]=MAX(V_QOS_DISK_CAPACITY_GBYTES[sampletime]) &&

V_QOS_DISK_CAPACITY_GBYTES[C_ContainsStringTarget]=TRUE;V_QOS_DISK_CAPACITY_GBYTES[samplevalue])

)

topic Re: How to create a virtual column of a virtual table to use in metrics in DAX Commands and Tips

How to create a virtual column of a virtual table to use in metrics

Re: How to create a virtual column of a virtual table to use in metrics