it Possible to do Dax Calculations when we use two Direct Query Tables?

[athuiraps]

Is it Possible to do Dax Calculations when we use two Direct Query Tables?

I have two direct query data sources, am trying to create a calculated column using two fields from that.

While using the DAX function "RELATED" to I receive an error.

 DATEDIFF(RELATED('Quarter dates'[End Date]]),Effectivedatetable[EffectiveDate],DAY)+1

Both tables are in direct query mode.

Relationship: Quarter dates (one) has a relationship with Effectivedatetable (many) (This two tables are from two different data source.)

Cardinality = Many to One (*:1)

Cross filter direction = Both

Make this relationship active = Checked

[v-luwang-msft]

Hi @athuiraps ，

DAX is available, but there are certain time intelligence DAX features that you cannot use such as every year, month over month, same period, and last year. to learn more direct query limitations,you could  click here to read Official Article .

Also about the error you encountered， you must use RELATEDTABLE instead of related due to the relationship between Quarter dates  and Effectivedatetable  is one -> many. 

refer :https://community.powerbi.com/t5/Desktop/Using-RELATED/m-p/7994  

https://community.powerbi.com/t5/Desktop/Lookups-when-RELATED-doesn-t-work/m-p/1083795#M501870

Wish it is helpful for you!

Best Regards

Lucien

[m3tr01d]

@athuiraps This seem to be an issue with composite model and DirectQuery. It doesnt seem to have been solved yet.

https://community.powerbi.com/t5/Issues/DAX-function-quot-RELATED-quot-does-not-work-between-DirectQ...

[m3tr01d]

@athuiraps First, is there a reason why you are using Direct query instead of Import mode?

[athuiraps]

Ys, Becauue our datasource is a power bi service live connection model(powerbi dataset).