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.
