operations with directQuery tab

[Anonymous]

Hello, I want to ask you for help.

I have tab1 on local and tab2 with directQuery connection.
In tab1 is column "project_number" with a lot of duplicities. In tab2 is also "project_number" but there are duplicities only for values blank and "-".
I need to make connection between tab1 and tab2 to see related "parameter" value in tab1 in all project numbers out of blank and "-". Same like in tab1 on the right side of picture.  
I tryed to create calculated column with IF to skip values with blank and "-" and with LOOKUPVALUE. It works fine, but during publishing it  alerted me that I used data from directQuery tab2 in calculated column, and it will make failure during datarefresh.
Thank you  for help

[Anonymous · Accepted solution]

Hi @Anonymous ,

Below is my table1:

Below is my table2:

The following DAX might work for you(try to create a measure and a index column):

Measure = 
  var tab_1 = SELECTEDVALUE(Tab1[Project_number])
  return
    IF(tab_1<> "-" && tab_1<>" " , LOOKUPVALUE('Table'[parameter],'Table'[project_number],tab_1)," ")

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Based on what I got. Join both direct query tables on the project number and use drill through.

Or have a project table with a distinct Project and join with both tables. Use the project number from that table in visual and then you can use that slicer or drill through

[Anonymous]

Thank you, I will try it.

[Anonymous]

Is here somebody how can help me? Please