Need to use slicer value on mutilple query fields

[curtismob]

For simplicity's sake, I have a home sales count report showing week-to-date sales and closing counts for homes that have sold or closed during a given week.  To simplify this I am using a date dimension that gives me the week number of the year, so I can easily check if the sales date or close date week number is equal to the given week number.  I created an additional query to use for the slicer,  that contains the list for week numbers for the year.  My issue is when creating the relationship between the week number list and the sales/closings query, I have to make two separate relationships 1) between the week number list and sales week number and 2) between the week number list and closing week number, but only one relationship  can be active.  How can I filter for both using a slicer?

Example:

Week Number List  for slicer (including year)

201601

201602

201603

Sales Week Numbers

Lot       Week

Lot 1    201602

Lot 4    201602

Lot 5    201603

Closings Week Numbers

Lot       Week

Lot 7    201602

Lot 8    201603

Lot 9    201603

If I select week 201602 from the Week Number list slicer, I would want to see

Sales      = 2   (Lots 1 and 4)

Closings = 1  (Lot 7)

[v-yuezhe-msft]

Hi @curtismob,

Could you please describe more details about your requirement? How do you create relationships between these tables? Based on my test, when I add a slicer using your Week Number List, it can filter both Sales and Closings. For more details, please check the example in the attached PBIX file.

Thanks,
Lydia Zhang

[curtismob]

Hi @v-yuezhe-msft,

Thank you for the response and the attached pbix example.  My issue is the sales and closing week numbers are in the same query/data set.  Sorry my examples did not depict this clearly.

Example should have been:

Week Numbers

Lot       Sales Week     Close Week

Lot 1    201602            0

Lot 4    201602            0

Lot 5    201603            0

Lot 7    201508            201602

Lot 8    201509            201603

Lot 9    201509            201603

If I select week 201602 from the Week Number list slicer, I would want to see

Sales      = 2   (Lots 1 and 4)

Closings = 1  (Lot 7

[v-yuezhe-msft]

Hi @curtismob,

You can create a new table using the following formula to include Week Numbers and Close Week. Then create active relationship between Week Number List and the new table, and create the visuals in my first reply.

Table = FILTER(ALLEXCEPT(Salesandclosings,Salesandclosings[Sales Week]),CONTAINS('Week Number List','Week Number List'[Column],Salesandclosings[Close Week]))

For more details, please review the example in the attached PBIX file.

Thanks,
Lydia Zhang

[curtismob]

@v-yuezhe-msft,

I would like to look at the pbix to get a better understanding of how you are using the new table.  Unfortunately, when I try to download the latest attached file it is a zip file and no pbix file is included.  In the meantime, I will use your example to see if I can make it work to resolve my issue.

Thank you again,

Curtis

[v-yuezhe-msft]

Hi @curtismob,

Please check if you can now download the attached PBIX file from the link below. I upload it again.

https://1drv.ms/u/s!AhsotbnGu1NogW3HfzK_He9d09I4

Thanks,
Lydia Zhang

[curtismob]

Hi @v-yuezhe-msft,

I was able to download your latest pbix example and I'm not sure I follow how I would use another table to resolve my problem.  I created a simple pbix in an attempt to better convey my challenge.  I want to change the "Sales/Closings Summary" table dynamically, as the week selection is changed in the Slicer.  I would like all WTD, MTD, and YTD counts to change accordingly. 

Since I am unable to attach the pbix to this post, what is the easiest way to make it available for your review?

Regards,

@curtismob

[v-yuezhe-msft]

Hi @curtismob,

You can upload your PBIX file to OneDrive and post the shared link of the PBIX file here. 

Thanks,
Lydia Zhang

[curtismob]

Hi @v-yuezhe-msft,

I'm not sure if I did this right, I haven't used OneDrive before, but try the link below to see if the pbix is there.

https://1drv.ms/u/s!AoJaXe4kxBJ-boeRM4WoFppiJ9o

Thank you,

Curtis

[curtismob]

Hello, finally getting back to this.  Now that I have had more time to work with Power BI, I think I may be able to ask this question in a more simple matter.

Can I use a single date in a Slicer and/or DAX, to filter visual data by 2 or more dates?

Example:

Slicer date = 01/31/2017

           Date1            Date2

Row1  01/15/2017   01/31/2017

Row2  01/31/2017   01/25/2017

If I was counting the dates that match the slicer date, then the visual data would only count 1 for Date1 and 1 for Date2

Hopefully this makes sense, any help would be greatly appreciated.

@curtismob