Forum Discussion
Crossfilter can only use the two columns references participating in relationship error
Hi all,
I have table that has 700k rows like,
Oag1:
And I duplicate that.
Oag 2:
And I want to crossjoin these tables with conditions as a calculated table.
I used this formula for this,
CrossjoinTable =
Filter(
Filter(
Filter(
Filter(
Crossjoin(Oag 1, Oag 2),
[Carrier 1] = [Carrier 2]),
[Arr 1] = [Dep 2],
[Zaman1] < [Zaman2],
[Zaman2] - [Zaman1] < 0.5))))
But it is so slow. I Think dax calculates inner first. It crossfilter two tables (700k x 700k) and then filtering.
So I want to change my code using calculatetable. Bc I want to filter first then crossjoin operation.
I tried this.
CrossJoinWithCalculateTable =
Calculatetable(
Crossjoin(Oag 1, Oag 2),
Crossfilter(Oag 1[Carrier 1] = Oag 2[Carrier 2],Both),
Crossfilter(Oag 1[Arr 1] = Oag 2[Dep2],Both))
I get this error:
"Crossfilter can only use the two columns references participating in relationship."
How can I solve this problem? Or how formula do I use for this operation?
Thank you.
2 Replies
- akay23
Helper I
Hi all,
I created a Key for each table.and I use thi formula;
FILTER( FILTER( FILTER( CALCULATETABLE(CROSSJOIN('oag 1','oag 2'), USERELATIONSHIP('oag 1'[Key 1],'oag 2'[Key 2])), 'oag 1'[Key 1] = 'oag 2'[Key 2]), 'oag 2'[zmn2] > 'oag 1'[zmn1]), 'oag 2'[zmn2] - 'oag 1'[zmn1] < 2)This is so faster right now. But it is not enough yet.
I want to put filters into calculatetable function.I keep researching 🙂
- akay23
Helper I
Hi again,
I found naturaliinerjoin in dax and then I learned merge in query just do that. I am still learning. thanks