Forum Discussion

nerra's avatar
nerra
Icon for Helper II rankHelper 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.FullDateAlternateK...
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

nerra's avatar
nerra
Icon for Helper II rankHelper 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
    Icon for Microsoft Employee rankMicrosoft 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

    • nerra's avatar
      nerra
      Icon for Helper II rankHelper II
      8 years ago

      Thanks!!!

  • nerra's avatar
    nerra
    Icon for Helper II rankHelper II
    8 years ago

    The good news is that this measure is working

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

     

    and when i added the page filter for the upper value of TestDate2=2005-01-01 it returned correct results. 

     

    But, I'm still wondering if I can get this to work in the measure itself. 

    • Stachu's avatar
      Stachu
      Icon for Community Champion rankCommunity Champion
      8 years ago

      what exactly is the output that you expect?
      from the SQL you provided it seems the output should be a table - all values in a.FullDateAlternateKey where a.testdate is before '2005-01-01', is that correct?
      measure will only produce scalar. you could concatenate the multiple dates into scalar, or you could create calculated table. which one are you looking for?

       

      the syntax you proivded in last post is refering to calculated column, not a measure, correct?

      • nerra's avatar
        nerra
        Icon for Helper II rankHelper II
        8 years ago

        I guess I am exploring both options. 

         


        select --max(a.FullDateAlternateKey),
        a.testdate,
        b.testdate2
        from [AdventureWorksDW2014].[dbo].[DimDate] a
        cross join [AdventureWorksDW2014].[dbo].[DimDate2] b
        where a.[TestDate]<=b.[TestDate2]
        and b.TestDate2='2005-01-01'
        order by a.testdate
        --group by a.testdate, b.testdate2

         

        PRODUCT: all testdate values that satisfy the conditions (image1)

         

        I'm creating measures. not calulated columns. And in this particular case I used:

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

         

        AND VISUAL LEVEL FITLER to set the TestDate2=2005-01-01

         

        Offcourse, it grouped the values so I get only distinct rows. (image 2)