Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
@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
Are we joining on key = [Store ID] & "-" & [Month] or the Index Column or both?
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |