Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
)
)
)
Solved! Go to Solution.
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
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
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
Any solutions? I tried a bunch, did not work so far.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
74 | |
63 | |
51 | |
47 |
User | Count |
---|---|
211 | |
85 | |
64 | |
59 | |
56 |