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.
Post ProdigyHi,
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 :
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 :
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.