File for test.pbix
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
)
)
)
