Forum Discussion
Helper IICrossjoin measure with filter
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
Community Championwhat 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?
Helper III 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)