Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jianlong
Resolver I
Resolver I

Create a dynamic table from two separate tables

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
            )
        )
    )

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
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

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
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

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

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

jianlong
Resolver I
Resolver I

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors