cancel
Showing results for
Did you mean: Frequent Visitor

## Filter rows in a measure

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!  Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated! Announcements #### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world. #### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st! #### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better. Top Solution Authors
Top Kudoed Authors
Users online (1,704)