Forum Discussion
Creating dynamic table with SELECTEDVALUE?
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
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
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?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
I wanted to see this in a tabular format. Is that possible?
Measures can only return scalar values. So it would have to be something like
RETURN COUNTROWS(EXCEPT(table1, table2))
