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

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

I think it may be just syntax issue, try this

&& DimDate2[TestDate2]=DATE(2005,1,1)
nerra's avatar
nerra
Icon for Helper II rankHelper II
8 years ago

nope. it's not that. 

Error: 

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

    could you paste some smaple rows from both tables?

    also - why use cross join? wouldn't it be sufficent to just filter
    DimDate[TestDate]<=DATE(2015,1,1)
    the values both the field you're selecting and the field where you apply filter are from the same table, it seems it's making this whole example much more complex - or is it the point

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

    this is the point. The second condition is making shure that 2005-01-01 is the top limit. 

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

    the thing is in DAX you could achieve the same without the corssjoin just, filtering the DimDate[testdate]
    right now the approahc is a bit like this
    x<y=z, what I propose is x<z


    unless I am missing something, but without actually seeing the tables I cannot help more

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

    These two tables are unrelated, so it's not possible to have both measure from both tables in the same visualization.