cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Create a dynamic table from two separate tables

Hi all,

Trying to find a simple solution for this  problem, hope you can share some ideas, thanks.

1) "All" table is the master key table

2) 1:many relationship to "A"-"E" table,   these tables all have a different dimension, "A-Dim"..

3) The purpose if to find the intersection among "A"-"E", if a dimension is selected, the corresponding will intersect for cohort;

4) seletion of Dim slicers is random depending on different combinations, so I don't want to use all the possible combinations of isfiltered,  in this case, total 32 combinations.

My thoughts are:
1) if a table is filtered, then    _t_a = distinct(A[KEY]),  if not filtered,  then this table will not participate cohort intersection, so I will use _t_all = distinct('All'[Key]).

2), this works:

``````Intersection2 =

var _t_a = distinct(A[KEY])
var _t_b = distinct(B[KEY])
var _t_c = distinct(C[KEY])
var _t_d =  distinct(D[KEY])
var _t_e = distinct(E[KEY])
return

countrows(distinct(INTERSECT(INTERSECT(INTERSECT(INTERSECT(_t_a,_t_b),_t_c),_t_d),_t_e)))``````

3) however, this does not work, any idea?  thanks.

``````Intersection =
// create dynamic tables for each data table, if dim is filtered, then find distinct key; if not filtered (all for slicer), then use all table)
VAR _t_a =
SWITCH (
TRUE (),
ISFILTERED ( A[A_DIM] ), DISTINCT ( A[KEY] ),
DISTINCT ( 'All'[Key] )
)
VAR _t_b =
SWITCH (
TRUE (),
ISFILTERED ( B[B_DIM] ), DISTINCT ( B[KEY] ),
DISTINCT ( 'All'[Key] )
)
VAR _t_c =
SWITCH (
TRUE (),
ISFILTERED ( C[C_DIM] ), DISTINCT ( C[KEY] ),
DISTINCT ( 'All'[Key] )
)
VAR _t_d =
SWITCH (
TRUE (),
ISFILTERED ( D[D_DIM] ), DISTINCT ( D[KEY] ),
DISTINCT ( 'All'[Key] )
)
VAR _t_e =
SWITCH (
TRUE (),
ISFILTERED ( E[E_DIM] ), DISTINCT ( E[KEY] ),
DISTINCT ( 'All'[Key] )
)
RETURN
COUNTROWS (
DISTINCT (
INTERSECT (
INTERSECT ( INTERSECT ( INTERSECT ( _t_a, _t_b ), _t_c ), _t_d ),
_t_e
)
)
)``````

1 ACCEPTED SOLUTION
Community Support

Hi @jianlong ,

By my test, the dynamic table expression can't be used in the INTERSECT or FILTER function as a table anyway, actually a calculated table/column cannot be dynamic, as they can only compute during data refresh.
Best Regards,
Community Support Team _ kalyj

4 REPLIES 4
Community Support

Hi @jianlong ,

By my test, the dynamic table expression can't be used in the INTERSECT or FILTER function as a table anyway, actually a calculated table/column cannot be dynamic, as they can only compute during data refresh.
Best Regards,
Community Support Team _ kalyj

Resolver I

@v-yanjiang-msft   Thank you for confirming this.

Community Support

Hi @jianlong ，

You're welcome! If you don't have other problems, would you like to mark your reply as the solution. Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

Resolver I

Any solutions?   I tried a bunch, did not work so far.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.