How apply filter in 2 different dimension tables

[e2liu77]

Hi ,

I have 2 tables, first table has "time", "A", "B", "C", "D" 4 columns. use time as filter.

Now I need to create a new table. New table column is based on ABCD calculation as below:

                     ColA                ColB              ColC

--------------------------------------------------------

Label1        B                           A                 C+D

Label2         D                         C                  A+B

Label3          0                         0                  A+B+C+D

I want filter "time" can be applied to the new table as well. When I create ColA ColB ColC as columns in new table, filter doesn't apply. Anyone has good idea how to acheive new table with filter applies in both old and new ones.

[Stachu]

I'm a bit confused here

1) is the new table already created, or do you need help in that?

2) if you filter time in the first table, which fields should pass the filter to second table?

in general calculated table will always be static and cannot be affected by slicer selection, changing filter etc.
you can create dynamic calculated table in a measure, but it will not be visible in the UI, it will be there just for the calculation to access

[e2liu77]

Hi Stachu,

Thanks for helping me.

My problem is that in table 2, the colA, colB and ColC calculation is column1 value related. with different column1 label value, the formula used are different. When I am using "if(table2[column1] = "label1, formular1, if(table2[column1] = "label2", formula2,......", it always reports error "A single value for column 'Column1' in table 'FunnelTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Anyway to put different calculation in 1 column based on column1 values?

[v-chuncz-msft]

@e2liu77,

Just change to MAX ( table2[column1] ).

[e2liu77]

Hi @v-chuncz-msft,

Sorry, I didn't get your point. How to use max() to switch between different text values in Column1?

[v-chuncz-msft · Accepted solution]

@e2liu77,

See code below.

IF (
    MAX ( table2[column1] ) =

[e2liu77]

Thank you, @v-chuncz-msft! It works now!