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

MonthStore IDSales
Mar 311110k
April 301111k
May 3111112k

 

STORE

 

Store IDStore nameMonth
11JudysMar 31
11JudysApril 30
11Judys & DaveMay 31

 

 

 

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.

3 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.