Hopefully a simple answer!!

[DavidH]

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.

[Anonymous · Accepted solution]

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