advice

2 Topics

Is this bad DAX, even though it works
I know that I always read about how tricky calculate can be and avoiding 'nested' calculates, so I'm looking for feedback. I have a measure that works, but there are nested Calculates, so I want to know if there is a better way. Model set-up table: Dates (well formed via Bravo) table: Projects Projects has a column 'Created Date' which is related to 'Date'[Date] via an inactive relationship. I'm returning the average number of projects created per month for the current year, so for 2025 that would be 'Count Projects' / 'Count Months' (2) (January & February). Here is the measure that works, but importantly you'll note that I'm nesting another measure [Projects Created] which itself is wrapped in a calculate. If I try to remove the calculate that wraps _result I get an OLE DB / ODBC error which cites [Projects Created] so I assume it has to do with the in-active relationship. If I try to direclty write the contents of the [Projects Created] measure into this measure it also fails. Main Measure: Avg. Proj Created by Mo current YR = VAR _currentYear = YEAR(TODAY()) VAR _Months = CALCULATETABLE( VALUES('Date'[Year Month]), FILTER('Date', YEAR('Date'[Date]) = _currentYear) ) VAR _result = CALCULATE( AVERAGEX( _Months, [Projects Created] ), USERELATIONSHIP('Date'[Date], 'Projects'[Created Date]) ) RETURN _result Nested Measure [Projects Created]: Projects Created = CALCULATE( COUNTROWS('Projects'), USERELATIONSHIP('Date'[Date], 'Projects'[Created Date]) )
Solved
rpiboy_1
1 year ago Place DAX Commands and Tips
622Views
0likes
3Comments
DAX Command Difference to Calculate Set of Data
Hello Power BI Expert, I am nearly completing my report in Power BI. However, it comes to my mind that I used two form of DAX when using "New Measure". Since I self-taught in Power BI, so here i am trying to understand what is the difference between these form of DAX writing (I use these two form the most): 1. Calculate(Countrows(table),Filter1,Filter2,Filter3)) 2. Countrows(Filter(table),Filter1&&Filter2&&Filter3) Both seem to provide same result in my data when calculating number of data in table. Another thing is, when i asked other people how to calculate data, they provide me with something like below. Looks like from SQL form of writing since I see that "window func SQL" (I might be wrong). the writing seems to be different than DAX. Is Power BI having more languange than DAX to be used in calculation? Thank you in advance.
Solved
Irwan
2 years ago Place DAX Commands and Tips
1KViews
0likes
2Comments