Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |