cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BeatGF
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:

2020-10-29 13_53_04-MfgKPI_v0.5 - Power BI Desktop.png

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!

1 REPLY 1
mahoneypat
Super User
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!