Forum Discussion
operations with directQuery tab
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
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.
4 Replies
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
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.
