Forum Discussion

anwarbi's avatar
anwarbi
Helper III
2 years ago

Create a filtered calculated table based on slicer value

Hello,

 

I am trying to create a calculated table based on a data table loaded in power bi.

 

Can someone please help me fix the below dax, I am trying to replace the hard coded "ABC", with a dynamic selection based on a slicer, not sure if this is possible.

 

FilteredTable = SUMMARIZE ( FILTER ( 'Table3', Table3[name] = "ABC") ), Table3[name], Table3[Country], Table3[companies], Table3[Group] )

 

Appreciate your help.

 

Thanks,

Anwar

Need Help

6 Replies

  • Thennarasu_R's avatar
    Thennarasu_R
    Responsive Resident
    2 years ago

    Hi anwarbi 

    Calculated table we can't create the dynamic based on the slicer. The reason is calculated table is like a data table based raw table you can build calculated table  . Slicer is like front end It will not effect with backed end tables.
    We can keep static filter in calculated table.

    Thanks,
    Thennarasu R

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    Hello anwarbi 

    build a table based on a slicer selection makes no sense to me. As the table will get refresh only when data is refreshed and not when a user change the selection in the slicer.

     

    Instead if you need to use it on the fly, to make some calculation that can be OK.

     

    To get the value selected from the user you need to use SELECTEDVALUE ('table'[column])

     

    Hope I made my point

    • anwarbi's avatar
      anwarbi
      Helper III
      2 years ago

      Thanks for your response.

       

      The reason I want to create a filtered table based on the slicer value is that then I need to use the filtered data as a vlook up source in another data table.

       

      I have tried using  SELECTEDVALUE ('table'[column]), but this does not yield any result as the table is blank.

       

       

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi anwarbi - it is possible, can you please create a seperate table for the slicer values 

    SlicerTable = DISTINCT('Table3'[name])

    Now lets create a measure to filter your data dynamically based on the slicer selection using selectedvalue function

    SelectedName =
    SELECTEDVALUE(SlicerTable[name])

     

    Since calculated tables can't use slicer values directly, you'll instead use a table visual in the report

    FilteredTableMeasure =
    CALCULATETABLE(
    SUMMARIZE(
    'Table3',
    'Table3'[name],
    'Table3'[Country],
    'Table3'[companies],
    'Table3'[Group]
    ),
    'Table3'[name] = [SelectedName]
    )

    Hope this works. please check 

    • anwarbi's avatar
      anwarbi
      Helper III
      2 years ago

      Hi Rajendra,

       

      Thanks for your help!

       

      I have used followed the instructions as directed, but i am getting the following error.

       

      A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

       

      The dax i am using is as follows:

       

      FilteredTableMeasure =
      CALCULATETABLE(
      SUMMARIZE(
      'Table3',
      'Table3'[name],
      'Table3'[Country],
      'Table3'[companies],
      'Table3'[Group]
      ),
      'Table3'[name] = [selectedname]
      )

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi anwarbi ,

    Based on the description, try to transform data in power query editor.

    Right click query and select duplicate.

    Then, select table name is ABC.

    Close and apply.

    Besides, you can raise a new idea and add the comments there to make the feature coming sooner.

    Home (microsoft.com)

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.