Create a Calculated Column from Different Tables in DirectQuery Mode

[abhay03]

I have two tables that are connected from Azure SQL Database using DirectQuery mode.

The cardinality between these two tables is Many to One (*:1). These tables are joined on ID field and I want create to a calculated column using these two tables which satisfies the below SQL query:

select distinct 
case when a.Unit is NOT NULL then 'Express' Else 'Standard' End as Unit_Flag,
from DB.table1 b LEFT OUTER JOIN DB.table2 a ON a.ID = b.ID and a.levelunit='High'

I looked at several solutions available in community but they did not seem to work with DirectQuery mode. Any help here would be very much appreciated.

[parry2k]

Try following, add new measure in Table 1 with following DAX:

Flag = 
var x =  CALCULATE(FIRSTNONBLANK(Table2[LevelUnit],1), Filter(Table2, Table2[LevelUnit]="High"))
return if(x = BLANK(), "Standard", "Express")

[abhay03]

Thanks @parry2k, your solution worked well. Only challange now I have is to show this Flag measure into a Slicer and since measures can't be used in filters, this has to be calculated into or converted into a column...

[v-shex-msft]

Hi @abhay03,

Based on test, I found a lot of dax functions not available on calculated column who create in directquery mode.
I think you had to use these functions in measure.

Regards,

Xiaoxin Sheng

[TomMartens]

Hey @abhay03,

you can try this Go To

File -> Options and Settings -> Options -> Direct Query

and enable "Allow unrestricted measures ..."

and try to create a clacultaed column, please be aware, that this may have an impact on your query performance.

So, do not forget that you created a calculated column, in case there will be a performance degradation over time, with growing number of rows in your Azure SQL tables.

Regards

Tom

[abhay03]

Hi @TomMartens,

I have enabled the "Enable unrestricted measures in DirectQuery mode" but as @v-shex-msft said many functions are restricted in DirectQuery mode for a calculated column.

I want to calculate the above DAX query as a column to be able to use that in the filter/slicer which I'm not able to achieve.