Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

dynamic tables based on slicer’s selected values

We have Power BI solution with the Model in Import mode. There is a special requirement to get dynamic tables based on slicer’s selected values. Desired output is union of two dynamic tables as in below diagram. Highlighted is the union.

Table 1 is the part of Model (in relationship with other tables)

Table 2 is unrelated table.

We tried Below DAX to generate dynamic table. It works fine with hardcoded value but does not recognize values selected from slicer.

 

Following working with hardcoded values

MergeSOV_Periltbl =

VAR SelectedProperty =

    CALCULATETABLE (values(PropertySOV),PropertySOV[PropertyName]

in  {"A","B"})

   

var SelectedPerils=CALCULATETABLE (values(Perils),Perils[PerilName]

          in  {"GLORIA","DAVID"})

 

VAR UnionResult = UNION( SelectedProperty, SelectedPerils)

 

RETURN

    UnionResult

 

Other syntax is

VAR SelectedProperty =CALCULATETABLE (filter(all(Property),CONTAINSROW({"A","B"},Property[PropertyName])))

 

Following Dynamic Values selected from slicer Is not working

MergeSOV_Periltbl =

VAR PropertySlicer = IF(ISFILTERED(Property),  

CONCATENATEX(ALLSELECTED(Property), PropertySOV[PropertyName],","),"(All)")

 

VAR PerilsSlicer = IF(ISFILTERED(Perils),  

CONCATENATEX(ALLSELECTED(Perils), Property[PerilsName],","),"(All)")

 

 

var SelectedProperty =CALCULATETABLE (values(Property),Property[PropertyName]

    in  { PropertySlicer })

 

 

var SelectedPerils =CALCULATETABLE (values(Perils), Perils [PerilName]

          in  { PerilsSlicer })

 

VAR UnionResult = UNION( SelectedProperty, SelectedPerils)

 

RETURN

    UnionResult

 

Please suggest Solution

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    HI Anonymous,

    Current power bi does not support creating dynamic calculated column/table based on filter/slicer. They are working on the different levels and you can't use the child to affect their parent level.

    Notice: the data level of power bi.

    Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

    Regards,

    Xiaoxin Sheng