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.