Forum Discussion
Conditional table using Dax / query on the fly
I have a tables as below :
Table 1 - granularity level is VID,NID - unique combination key
| VID | NID | Age | Height |
| a | 1 | 34 | 167 |
| a | 2 | 23 | 156 |
| b | 3 | 54 | 189 |
| b | 4 | 65 | 187 |
| b | 5 | 23 | 160 |
| c | 6 | 12 | 189 |
| d | 7 | 56 | 188 |
Table 2 - granularity level is VID,NID,condition- unique combination key. note VID , NID can repeat with different conditions
| VID | NID | Condition |
| a | 2 | x1 |
| d | 7 | x2 |
Target is to achieve tables ( caluculated or temp ) after following options to filter
FILTERS (only for e.g. set below)
Age <=50
Height <=190
Condition = X1 [this is required as anti filter , i.e. remove not keep]
Expected tables to be generated and refreshed (as filters changed)
Table 3 - filtered table
| VID | NID | Age | Height |
| a | 1 | 34 | 167 |
| b | 5 | 23 | 160 |
| c | 6 | 12 | 189 |
a,2 was removed as it had x1 condition and b4,b3 were removed due to age filter
Table 4 ( Except (all ( Table 1) , table 3) : i.e. all records that were filtered out from main table
| VID | NID | Age | Height |
| b | 3 | 54 | 189 |
| b | 4 | 65 | 187 |
| a | 2 | 23 | 156 |
Once these two tables are generated, i will create calculations based on that. how do i create these tables as temp or permamnent tables in power bi ( interactively updating as filters change )
2 Replies
Hi pbhat89 ,
So, you want to create table3 and table4 by Dax or Query? But table3 is based on filter and can't be dynamic if you want it as a fact table. Please let me know if I misunderstand your meaning.
Best Regards,
Jay
exactly - tables 3 and 4 are dynamic as they change based on filters. I am okay it not being a fact table as long as i can refer to the columns from these tables. Hence okay even somehow i can create them as temp tables.
