topic Re: Creating dynamic table with SELECTEDVALUE? in DAX Commands and Tips

Creating dynamic table with SELECTEDVALUE?

afaro — Fri, 29 Mar 2024 21:58:24 GMT

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?

Re: Creating dynamic table with SELECTEDVALUE?

lbendlin — Fri, 29 Mar 2024 22:00:45 GMT

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

Re: Creating dynamic table with SELECTEDVALUE?

Anonymous — Mon, 01 Apr 2024 08:49:50 GMT

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

Re: Creating dynamic table with SELECTEDVALUE?

afaro — Mon, 01 Apr 2024 14:38:16 GMT

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?

Re: Creating dynamic table with SELECTEDVALUE?

lbendlin — Mon, 01 Apr 2024 19:11:43 GMT

Measures can only return scalar values. So it would have to be something like

RETURN COUNTROWS(EXCEPT(table1, table2))

Re: Creating dynamic table with SELECTEDVALUE?

Anonymous — Tue, 02 Apr 2024 05:42:21 GMT

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

Re: Creating dynamic table with SELECTEDVALUE?

afaro — Tue, 02 Apr 2024 16:00:07 GMT

I wanted to see this in a tabular format. Is that possible?

Re: Creating dynamic table with SELECTEDVALUE?

lbendlin — Tue, 02 Apr 2024 17:10:43 GMT

Measures can only return scalar values.