Filter rows in a measure

Anonymous — Thu, 29 Oct 2020 12:59:39 GMT

I calculate an average value, but not all rows are filled in the original data. These I want to filter this and only return rows containing data in all columns.

Here the data after the caluculation of the average:

I can achieve this by setting a filter in the "filter pane" in the Desktop App.

But, how can I do this in DAX?

Here my code:

LocalCountOfAccicentsAvg = VAR Known = FILTER ( SELECTCOLUMNS ( ALLSELECTED ('Date'[Date]), "Known[X]", 'Date'[Date], "Known[Y]", CALCULATE(sum(KpiData[LocalCountOfAccidents])) ), AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) ) ) VAR Count_Items = COUNTROWS ( Known ) VAR Sum_X = SUMX ( Known, Known[X] ) VAR Sum_X2 = SUMX ( Known, Known[X] ^ 2 ) VAR Sum_Y = SUMX ( Known, Known[Y] ) VAR Sum_XY = SUMX ( Known, Known[X] * Known[Y] ) VAR Average_X = AVERAGEX ( Known, Known[X] ) VAR Average_Y = AVERAGEX ( Known, Known[Y] ) VAR Slope = DIVIDE ( Count_Items * Sum_XY - Sum_X * Sum_Y, Count_Items * Sum_X2 - Sum_X ^ 2 ) VAR Intercept = Average_Y - Slope * Average_X VAR AllData = SUMX ( DISTINCT ( 'Date'[Date] ), Intercept + Slope * 'Date'[Date]) RETURN AllData

Thanks!

Re: Filter rows in a measure

mahoneypat — Thu, 29 Oct 2020 22:48:08 GMT

I think the only issue with your code is not having the [ ] around your table names in the ISBLANKs. I would not use [] in your virtual column names, to simplify things. I would just use "KnownY" and "KnownX" when naming your columns in the SELECTCOLUMNS.

Regards,

Pat

topic Filter rows in a measure in DAX Commands and Tips

Filter rows in a measure

Re: Filter rows in a measure