cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 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.FullDateAlternateKey ,a.testdate, b.testdate from
where a.[TestDate]<=b.[TestDate]
and b.TestDate='2005-01-01'

p.s. I'm trying to avoid calculated tables and columns

Thanks,

N

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Crossjoin 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 Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
14 REPLIES 14
Super User II

## Re: Crossjoin measure with filter

check this pattern

https://www.sqlbi.com/blog/marco/2016/07/26/leverage-intersect-to-apply-relationships-in-dax/

Thank you for the kudos 🙂

Proud to be a Datanaut!

Helper I

## Re: Crossjoin measure with filter

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

Helper I

## Re: Crossjoin measure with filter

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.

Super User II

## Re: Crossjoin measure with filter

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?

Thank you for the kudos 🙂

Proud to be a Datanaut!

Helper I

## Re: Crossjoin measure with filter

I guess I am exploring both options.

select --max(a.FullDateAlternateKey),
a.testdate,
b.testdate2
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)

Helper I

## Re: Crossjoin measure with filter

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

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

?

Super User II

## Re: Crossjoin measure with filter

I think it may be just syntax issue, try this

`&& DimDate2[TestDate2]=DATE(2005,1,1)`
Thank you for the kudos 🙂

Proud to be a Datanaut!

Helper I

## Re: Crossjoin measure with filter

nope. it's not that.

Error:

Super User II

## Re: Crossjoin measure with filter

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

Thank you for the kudos 🙂

Proud to be a Datanaut!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors