Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.FullDateAlternateKey ,a.testdate, b.testdate from
[AdventureWorksDW2014].[dbo].[DimDate] a
cross join [AdventureWorksDW2014].[dbo].[DimDate2] b
where a.[TestDate]<=b.[TestDate]
and b.TestDate='2005-01-01'
p.s. I'm trying to avoid calculated tables and columns
Thanks,
N
Solved! Go to Solution.
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
check this pattern
https://www.sqlbi.com/blog/marco/2016/07/26/leverage-intersect-to-apply-relationships-in-dax/
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
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
Thanks!!!
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.
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?
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)
Basically, what is the reason why I'm not able to add additonal filtering inside the measure:
&& DimDate2[TestDate2]=2005-01-01
?
I think it may be just syntax issue, try this
&& DimDate2[TestDate2]=DATE(2005,1,1)
nope. it's not that.
Error:
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
this is the point. The second condition is making shure that 2005-01-01 is the top limit.
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
These two tables are unrelated, so it's not possible to have both measure from both tables in the same visualization.
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |