Forum Discussion

afaro's avatar
afaro
Helper III
2 years ago
Solved

Creating dynamic table with SELECTEDVALUE?

Is it possible to create dynamic tables this way? 

I have written dax for creating tablewhich works perfectly fine when I manually provide the filters (which then makes it a static table) but when I try to filter the data and create table using value obtained from SELECTEDVALUE then it just returns me an empty table. 

 

So, can SELECTEDVALUE be used to create dynamic tables? 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi afaro ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    Added products = 
VAR _month =
    SELECTEDVALUE ( 'Date'[Month] )
VAR table1 =
    CALCULATETABLE (
        VALUES ( 'Data'[Product] ),
        FILTER ( 'Data', 'Data'[Month] = _month )
    )
VAR table2 =
    CALCULATETABLE (
        VALUES ( 'Data'[Product] ),
        FILTER ( 'Data', 'Data'[Month] = _month - 1 )
    )
VAR _tab =
    EXCEPT ( table1, table2 )
RETURN
    CONCATENATEX ( _tab, [Product], "," )

    Best Regards

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    SELECTEDVALUE is meaningless without a filter context. You can create "dynamic tables"  but only as an intermediate step in a measure calculation.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    lbendlin  Thanks for your contribution on this thread.

    Hi afaro ,

    lbendlin have provided a explanation for your problem. To further elaborate on @lbendlin’s explanation, I would like to add that if you are creating a calculated table, the values within it will remain static and will not be influenced by user interactions such as filters or slicers. This is an important aspect to consider when working with Power BI.

    As suggested by lbendlin , a potential solution could be to create a measure. This measure can then be applied to a table visual along with other relevant fields. This approach will allow the data to be displayed dynamically, adapting to user interactions.

    Measure =
VAR _seltype =
    SELECTEDVALUE ( 'Table1'[slicerfield] )
RETURN
    CALCULATE (
        SUM ( 'Table2'[value] ),
        FILTER ( 'Table2', 'Table2'[type] = _seltype )
    )

    Best Regards

    • afaro's avatar
      afaro
      Helper III
      2 years ago

      Thank you for the answer. 

      I wanted to know how I can update the solution to a measure if I want to find out whether a specific product was present in the month of April but not in the month of March. A product is present if it has a corresponding row for it in that month. 

      I wrote something which works for table (but doesn't work dynamically for any month because of the limitation on SELECTEDVALUE) 

      Added =

var table1 = CALCULATETABLE(SUMMARIZE('Data', 'Data'[Product]), 'Data'[Month] = 4)
var table2 = CALCULATETABLE(SUMMARIZE('Data', 'Data'[Product]), 'Data'[Month] = 3)

RETURN
EXCEPT(table1, table2)


      How to modify this into a measure using SELECTEDVALUE and CALCULATE? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi afaro ,

        I created a sample pbix file(see the attachment), please check if that is what you want.

        Added products = 
VAR _month =
    SELECTEDVALUE ( 'Date'[Month] )
VAR table1 =
    CALCULATETABLE (
        VALUES ( 'Data'[Product] ),
        FILTER ( 'Data', 'Data'[Month] = _month )
    )
VAR table2 =
    CALCULATETABLE (
        VALUES ( 'Data'[Product] ),
        FILTER ( 'Data', 'Data'[Month] = _month - 1 )
    )
VAR _tab =
    EXCEPT ( table1, table2 )
RETURN
    CONCATENATEX ( _tab, [Product], "," )

        Best Regards