topic Filtering Card Visual in DAX Commands and Tips

Filtering Card Visual

equetzal — Thu, 29 Jul 2021 01:48:03 GMT

Hi,

I am currently trying to use a card visual to display a measure that calculates the max of another measure as can be seen below, but i ran into a problem, the card can be filtered from the filter panel and since some of the data has spikes the spike is shown. Any suggestions on how I can filter out these spikes from the measure. Any help would be apriciated.

the card data has the max imbalance and and I have filtered the line chart using the filter panel but I can't do the same for the card.

Below is the DAX I used to show the maxium voltage imbalance from the V-imbal measure

MAX V-BMP01-IMBAL = 
Var MaxVImbalBMP01 =
MAXX(
 V_BMP,
 'Voltage Measures'[V-BMP01-IMBAL]
)
RETURN
MaxVImbalBMP01

Below is the DAX for the line chart that shows V-imbalance measure

V-BMP01-IMBAL = 
VAR ValueTableBMP01 =
UNION(
 ROW("BMP01",AVERAGE(V_BMP[V-BMP01-AB])),
 ROW("BMP01",AVERAGE(V_BMP[V-BMP01-BC])),
 ROW("BMP01",AVERAGE(V_BMP[V-BMP01-CA]))
)
VAR BMP01ROWMAX = 
MAXX(
 ValueTableBMP01,[BMP01]
)
VAR BMP01ROWAVE =
AVERAGEX(
 ValueTableBMP01, 
 [BMP01]
)
VAR BMP01IMBAL = 
(BMP01ROWMAX - BMP01ROWAVE) / BMP01ROWAVE
Var MaxVImbalBMP01 =
MAXX(
 V_BMP,
 BMP01IMBAL
)
RETURN
MaxVImbalBMP01

Re: Filtering Card Visual

littlemojopuppy — Thu, 29 Jul 2021 14:00:17 GMT

Hi equetzal

There's a couple things you can do. Calculate as variables either: the average and standard deviation of the data OR a high and low percentile (maybe 10th and 90th?).

When you're calculating the average you can filter for values between (average +/- 2 standard deviations) or (high and low percentiles). Either way will discard outliers.

Hope this helps!

Re: Filtering Card Visual

equetzal — Fri, 30 Jul 2021 16:06:30 GMT

Hi littlemojopuppy
Thanks for the suggestion I have tried but the card comeout blank could you be so kind as to give an example.

Re: Filtering Card Visual

littlemojopuppy — Fri, 30 Jul 2021 16:47:06 GMT

Hi equetzal Here's a sample from something similar I've done...

VAR PercentileCutoff = PERCENTILEX.INC ( CalculateDaysOpen, [DaysOpen], .99 ) RETURN AVERAGEX ( FILTER ( CalculateDaysOpen, [DaysOpen] >= 0 && [DaysOpen] <= PercentileCutoff ), [DaysOpen] )

Can you share your formula?

Re: Filtering Card Visual

equetzal — Fri, 30 Jul 2021 17:17:17 GMT

Hi littlemojopuppy
sure please see below

Re: Filtering Card Visual

littlemojopuppy — Fri, 30 Jul 2021 17:24:45 GMT

Surprised you're not getting an error. The PERCENTILEX.INC function requires a value between 0 and 1 for the third parameter. You entered 1.05.

If you want to exclude the outliers from the max calculation, calculate the percentile first, and then filter the table in the MAXX calculation. Use the code I provided as guidance for the order the steps should be in.