Solved: Crossjoin measure with filter - Page 2

nerra · ‎07-10-2018

Hello!

I have a hypothethical problem calculating a cross join between two unrelated date tables. Is it possible creating a measure that will return something like

select a.FullDateAlternateKey ,a.testdate, b.testdate from
[AdventureWorksDW2014].[dbo].[DimDate] a
cross join [AdventureWorksDW2014].[dbo].[DimDate2] b
where a.[TestDate]<=b.[TestDate]
and b.TestDate='2005-01-01'

p.s. I'm trying to avoid calculated tables and columns

Thanks,

N

v-yulgu-msft · ‎07-20-2018

Hi @nerra,

CrossJoinDate = calculate(MAX(DimDate[FullDateAlternateKey]);
FILTER(
CROSSJOIN(DimDate;DimDate2);
DimDate[TestDate]<=DimDate2[TestDate2]) && DimDate2[TestDate2]=(2005-01-01) )

i get the following error: A function filter has been used in a True/False Expression that is used as a table filter expression. this is not allowed

Please modify the formula as below:

CrossJoinDate =
CALCULATE (
    MAX ( DimDate[FullDateAlternateKey] );
    FILTER (
        CROSSJOIN ( DimDate; DimDate2 );
        DimDate[TestDate] <= DimDate2[TestDate2]
            && DimDate2[TestDate2] = DATE ( 2005; 1; 1 )
    )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Crossjoin measure with filter

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly