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

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!!!