Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculated Table Creation

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.

 

 

Need Help
Show and Tell

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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