Forum Discussion

akay23's avatar
akay23
Icon for Helper I rankHelper I
3 years ago

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

  • 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 🙂 

     

  • Hi again,
    I found naturaliinerjoin in dax and then I learned merge in query just do that. I am still learning. thanks