Calculated Table Creation

[Anonymous]

Hello,

I have an SQL query and there are many inner joins. 

But my problem is about an outer apply under the inner joins. 

You can find below example code:

outer apply (
select top 1 *
from XXX as X
where XXX.XID = Y.YID
and isnull(ZZ.DateField, getdate()) between AnotherDateField and isnull(OtherDateField, '2100-01-01')X

I thougt to use this outer apply code block as a new table in my power bi query and then I can use it as filter? Is it possible? And how can I create a calculated table with this query?

Thanks.

[v-xicai]

Hi @Anonymous   ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @Anonymous ,

You may create calculated table like DAX below.

New table= CALCULATETABLE('XXX', 'XXX'[XID]= 'Y'[YID], IF(ISBALNK('ZZ'[DateField]), TODAY(), 'ZZ'[DateField] ), IF(ISBALNK('AnotherDateField'[OtherDateField]), '2100-01-01', 'AnotherDateField'[OtherDateField] ) )

Then you filter the table visual which display the new created table above using Top N filter of Visual level filter in Filter pane.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , refer if this can help

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/