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

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)

 

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

Basically, what is the reason why I'm not able to add additonal filtering inside the measure:

&& DimDate2[TestDate2]=2005-01-01

 ?

  • 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