Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |