Forum Discussion

nerra's avatar
nerra
Helper II
8 years ago
Solved

Crossjoin measure with filter

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

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

14 Replies

    • nerra's avatar
      nerra
      Helper II
      8 years ago

      Hey!

      I created a measure.. something like this

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

       

      offcourse, it's not working. when i replace the ; with && like this

       

      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

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        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