Forum Discussion
Hopefully a simple answer!!
I have been struggling with arranging or segregating tables based upon values from another table.
I have got a simple example for illustration of my problem.
Table 1 is a main table that is brought in to PowerBI
Table 2 is a table I want to use to 'extract' or create a new table with those values shown out of Table 1
Table 3 would be the resulting table I am after
I have tried to use CALCULATETABLE using EXCEPT or FILTER but I am not able to get what I am after. I appreciate that using the EXCEPT context will give me everything except the values I want but is there any way I can get the values I want and ignore that ones not on the list (Table 2)?
I have no relationships set up on these so I am wondering if the TREATAS function could be used as a virtual relationship or if there is something that will simply capture this?
Table 2 could be a Table that is created using DISTINCT or VALUES from something else either outside or within PowerBI depending on the scenario I am after.
I'm not sure what you're exactly after... My guess is that you want to create a new table via DAX in the model based on Table1 and Table 2.
To create the new table is rather easy.
Table 3 = CALCULATETABLE( 'Table 1', TREATAS( 'Table 2', 'Table 1'[Code] ) )
Another way is to create a relationship from 'Table 1'[Code] to 'Table 2'[Code] (*:1) and then
Table 3 = CALCULATETABLE( 'Table 1', 'Table 2' )
Best
Darek
1 Reply
I'm not sure what you're exactly after... My guess is that you want to create a new table via DAX in the model based on Table1 and Table 2.
To create the new table is rather easy.
Table 3 = CALCULATETABLE( 'Table 1', TREATAS( 'Table 2', 'Table 1'[Code] ) )
Another way is to create a relationship from 'Table 1'[Code] to 'Table 2'[Code] (*:1) and then
Table 3 = CALCULATETABLE( 'Table 1', 'Table 2' )
Best
Darek