Re: Conditional tables using DAX or other methods

pbhat89 · ‎12-02-2020

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 )

v-yangliu-msft · ‎12-10-2020

Hi @pbhat89 ,

As far as I know, it seems that this requirement can not be implemented, because whether it is a table filtered from power query or exported to CSV after filtering, the table generated by Excel is a fixed table and will not change with the filter.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yangliu-msft · ‎12-06-2020

Hi @pbhat89 ,

Here are the steps you can follow：

1. Enter power query, home – merge queries as new through transform data, select the columns associated with the two tables, and perform left outer

Merge into table merge1:

2. Create calculated table.

Table3 =

var _1=SUMMARIZE('Merge1',

'Merge1'[VID],'Merge1'[NID],

"Age",CALCULATE(SUM('Merge1'[Age]),FILTER('Merge1','Merge1'[Age]<=50&&'Merge1'[Height]<=190&&'Merge1'[Table2.Condition]=BLANK())),

"Height",CALCULATE(SUM(Merge1[Height]),FILTER('Merge1','Merge1'[Age]<=50&&'Merge1'[Height]<=190&&'Merge1'[Table2.Condition]=BLANK())))

return FILTER(_1,[Age]<>BLANK())

The results of Table 3 are as follows：

Table4 =

var _1=

SUMMARIZE('Merge1',

'Merge1'[VID],'Merge1'[NID],

"Age",CALCULATE(SUM(Merge1[Age]),FILTER('Merge1',('Merge1'[Age]>=50 ||'Merge1'[Table2.Condition]="x1")&&'Merge1'[Table2.Condition]<>"x2")),

"Height",CALCULATE(SUM(Merge1[Height]),FILTER('Merge1',('Merge1'[Age]>=50 || 'Merge1'[Table2.Condition]="x1")&&'Merge1'[Table2.Condition]<>"x2")))

return FILTER(_1,[Age]<>BLANK())

The results of Table 4 are as follows：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

pbhat89 · ‎12-08-2020

Liu, thanks. But in your table 3 and 4 above you seem to be using fixed filter values i.e. Age >= 50. this the value which will be a filter in the dashboard which user can input , hence this has to be variable and not tied to 50. Hence the need to keep updating table 3, 4 as values change.

MFelix · ‎12-03-2020

Hi @pbhat89 ,

Not really sure about the final outcome you are expecting but making it a step by step you need to add the following measure to your model:

FIltering = 

var selectedNid = VALUES('Table (2)'[NID])
var selectedVID = VALUES('Table (2)'[VID])
Return
IF(SELECTEDVALUE('Table'[NID]) in   selectedNid && SELECTEDVALUE('Table'[VID]) in selectedVID; BLANK();1)

Now has you can see when you select the values on the slicers the table only gets the result you need.

My question is what is the calculation you need to have? you only show the filtered table result but what do you want to have in the end? Asking this because you will need to have a temporary table created but i without knowing what is the final result you need I cannot assist in helping you wiht the code since this depends on context.

Checkl PBIX file attach.

Believe this is repeated with the post below:

https://community.powerbi.com/t5/Desktop/Conditional-table-using-Dax-query-on-the-fly/m-p/1526109

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

pbhat89 · ‎12-03-2020

Thanks MFelix. There are various calculations that is difficult for me to describe in the example above - hence for me it is ideal to have temp / changing conditional tables based on which i can create those calculations else i have to use long NOT in , IF ELSE kind of statements . For better idea to you - two calculations is :

Distinct Count of NID ( table 3 ) - but excluding the common NID in table 4 - hence only "C" = Count = 1

Distinct Count of NID ( table 4 ) - but excluding the common NID in table 4 - hence only "B,A" = Count = 2

As mentioned, there are many other calculations from other values that i need to do. Hence i need table 3 , 4 to be created on the fly so that i can reference the columns directly from these tables that are updated each time a filter is applied. Is that possible ? Recently i tried to create a copy of table 1 , then use merge as new and selected left anti join - hoping that as i apply filters , i will be left with the right table but it shows 0 rows each time as if it doesnt detect the realtime applied filters.