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...
  • 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

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? 

lbendlin's avatar
lbendlin
Super User
2 years ago

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

 

RETURN
COUNTROWS(EXCEPT(table1, table2))