Forum Discussion
Post ProdigyDAX calulation Composite model
For the first question, the limitation of dax in direct query will not be affected by specific model(e.g.: composite model). The basic difference is differences between in-memory and DirectQuery mode. Please refer to doc: https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016#differences-between-in-memory-and-directquery-mode
For the second question, as you mentioned, SELECTEDVALUE() is not supported in direct query mode, VALUES() and MAXX() are supported in measure and query formulas only, not calculate columns. So the workaround is to change calculate column with measure in power bi or create the calculate field in SSAS and connect to AS model in live model.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support
For the first question, the limitation of dax in direct query will not be affected by specific model(e.g.: composite model). The basic difference is differences between in-memory and DirectQuery mode. Please refer to doc: https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016#differences-between-in-memory-and-directquery-mode
For the second question, as you mentioned, SELECTEDVALUE() is not supported in direct query mode, VALUES() and MAXX() are supported in measure and query formulas only, not calculate columns. So the workaround is to change calculate column with measure in power bi or create the calculate field in SSAS and connect to AS model in live model.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
admin_xlsiorHi,
thanks for the clarification. But now I'm confuse, how to change DAX that originaly for Calculated column to measures ?
For example if I have this table :
Item InvoiceDate Amount P-100267 17-Mar-16 200 P-100361 1-Jan-10 500 P-100362 1-Jan-10 100 P-100266 17-Mar-16 400 P-100265 17-Mar-16 800 P-100261 18-Mar-16 900 And I create a measure to get the input date from date slicer :
As of date = VALUES('Date'[DATE_])In normal practice I should create caluclated column, so each row has the date difference. Should be something like this :
Age = [As of date] - 'table'[InvoiceDate]
Then while I have the aging bucket like below:
Agegroup Min Max <20 days 0 20 21-100 days 21 100 >100 days 101 9999 So, with the infromation of [Age] per row from the 1st calculated column, the next calculated column will be something like :
AgeGroup = CALCULATE(VALUES(AgingBucket[Age Group]),FILTER(AgingBucket,Table[Age] >= AgingBucket[Min] &&Table[Age] < AgingBucket[Max]))By this usualy we can create a matrix something like :
Item <20 days 21-100days >100 days A amount amount amount B amount amount amount C amount amount amount So, how to turn both calculated column to measures ? and does it still make sense to produce that kind of aging report ?
Thanks,
- v-yuta-msft
If calculate column doesn't work, I would suggest you to create model in SSAS and then connect to SSAS with power bi.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.