Get multiple rows for slicer for selected value - Date

[earltom]

Hi

Trust you experts can help me

We have stores that we supply goods to and it often happens that stores are sold and then change name, if users we run a Report we like to see the Stores name as per the date it was run.

I can display the latest Store name using max measure, how can I display all the stores in the Visual as a Filter.

Dax only returns one value and not mulitple stores

FACT

Month	Store ID	Sales
Mar 31	11	10k
April 30	11	11k
May 31	11	112k

STORE

Store ID	Store name	Month
11	Judys	Mar 31
11	Judys	April 30
11	Judys & Dave	May 31

[v-binbinyu-msft · Accepted solution]

Hi @earltom ,

Please try below steps

1.my test table

FACT:

STORE:

Model:

2. add a slicer , card visual, create a measure and  add it to card

Store Name For Month =
VAR cur_month =
    SELECTEDVALUE ( STORE[Month] )
VAR cur_id =
    SELECTEDVALUE ( STORE[Store ID] )
VAR tmp =
    FILTER ( ALL ( STORE ), STORE[Store ID] = cur_id && STORE[Month] <= cur_month )
VAR final =
    CONCATENATEX ( tmp, [Month] & " " & [Store name], "
" )
RETURN
    final

 

Please refer attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@earltom , If the Month and Store ID are always available then you can have combined key in both tables and join

key = [Store ID] & "-" & [Month]

Join both tables on this Store to Fact 1-M

if dates/month in store are not regular. then we need have dates using month 

and add a new column to fact  (Add an index column in store table in power query)

New column in fact =

var _date = maxx(filter(Store, Store[Date] > fact[Date] && Store[Store ID] = fact[Store ID]) , Store[Date])

return

maxx(filter(Store, Store[Date] =_max && Store[Store ID] = fact[Store ID]) , Store[Index])

Join both table on index

Power Query- Index Column: https://youtu.be/NS4esnCDqVw

[earltom]

Are we joining on key = [Store ID] & "-" & [Month] or the Index Column or both?