Forum Discussion

earltom's avatar
earltom
Frequent Visitor
4 years ago
Solved

Get multiple rows for slicer for selected value - Date

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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's avatar
    earltom
    Frequent Visitor
    4 years ago

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