How to retreive multiple values from a table by sending more than 1 parameter as input DAX

[pradeep467]

Hi Experts,

I am facing some issues with lookup in Power Bi..I have my tables as

Week Dim  

Week	Period	Quarter	Year	Prior Year Week
202002	202001	202001	2020	201902
202003	202001	202001	2020	201903

Period Dim

Period	Quarter	Year	Prior Year Period
202001	202001	2020	201901
202002	202001	2020	201902

I have multi-select slicers on my report for year, period,qtr and week

My fact table is at a week granularity

For my Year over year calculations when user selects any period it has to lookup the week dimension using the Prior Year Period  from period Dim and return the list of weeks it has to aggregate.

I tried using lookup finction but it fails when more than 1 value is selected in slicer.

So if a user sleect 2 periods from period slicer, I need to lookup week dim and get 8 weeks for the selected periods..what functions can I use in order to get this functionality

[pradeep467]

At a high level what I am struggling to get through DAX is,

If a user selects 2 periods in the period slicer, I am looking for dax equivalent for below 2 queries

prior_year_periods=select prior_year_period from period_d where period_id=selectedvalues(period)

weeks_list=select week from week_d where period in prior_year_period_id

Any help would be really appeciated.

[mahoneypat]

This would be easier if you had a Date table, and Marked it As Date Table.  You could then use DAX Time Intelligence to make your calculation easier.  Your Date table can have columns for week, period, etc.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[pradeep467]

Thanks for the pointer Pat, but I am not sure how a Date table wil answer my problem as my fact table is at a week granularity. So I have week numbers stored as foreign keys and I am detrming prior year periods and weeks using week or period dimensions.

[mahoneypat]

A Date table could actually be used and have a column that matches you period column.  In any case, you should add an integer column to your Period table, and a column that has the year in integer form.  That will make DAX expressions easier so you can add/subtract weeks/years more easily.  A column that has a sequence of weeks throughout your whole date range would probably be useful too (for when you are subtracting weeks at the start of the year into the previous year, for example).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-easonf-msft]

Hi ,  @pradeep467 

The information you have provided is not making the problem clear to me. Can you please explain with an example.

PBIX/some sample data and expected result would help tremendously.

Best Regards,
Community Support Team _ Eason

[pradeep467]

Thanks for replying...so here is my case

my fact table is like

And my report is like

So as mentioned in my initial message, each period has 4 weeks..

so if user select 2 periods from the period slicer (202001,202002) , then we have to look up the Period dimenison to get the perior year periods for these 2 which in this case are 201901 and 201902.

Using these periods we have to look up the week dimenion to see what weeks falls under these 2 periods(201901-201908) and aggregate the Sales for those weeks as last year sales.

I used SELECTEDVALUE but it fails when a second value is selected in slicer.