Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kkassel
Frequent Visitor

week slicer that controls multiple columns from one dataset

I'm trying to figure out the most efficient way to get the result I need.  I've tried a couple different ways but with with no luck.  I have a dataset with a list of construction project account numbers along with their corresponding sign date, approval date, and start date.  I've added columns to compute the associated week number.

Table1.JPG

What I'm trying to do is create 3 table visuals that reside on the same page that are all control by 1 slicer.  The slicer would be the week number.  For example, I'd select week 39 on the slicer and would be the below results.

Table2.JPG

Most accounts will only appear on one table but some possibly on multiple.  You'll see that account 1943230157 appears on visual 1 and 2 but not on 3.  The sign week and approval week are both 39 but the start week is 41.

 

Any help on this would be much appreciated.  Let me know if you need any other information

 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @kkassel ,

 

Would you please follow the steps below:

 

1. Create a new table for slicer:

 

 

Slicer = DISTINCT(UNION(VALUES('Table'[Sign Week]),VALUES('Table'[Approval Week]),VALUES('Table'[Start Week])))

 

 

Capture7.PNG

 

2. Create three measures for table visual filter:

 

 

Measure1 = IF(MAX('Table'[Sign Week]) = MAX(Slicer[Week]),1,0)
Measure2 = IF(MAX('Table'[Approval Week]) = MAX(Slicer[Week]),1,0)
Measure3 = IF(MAX('Table'[Start Week]) = MAX(Slicer[Week]),1,0)

 

 

3. Add them to corresponding table visual:

Capture8.PNG

Capture10.PNG

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETXyIIOggoVBlI3dZJ...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

Hi @kkassel ,

 

Would you please follow the steps below:

 

1. Create a new table for slicer:

 

 

Slicer = DISTINCT(UNION(VALUES('Table'[Sign Week]),VALUES('Table'[Approval Week]),VALUES('Table'[Start Week])))

 

 

Capture7.PNG

 

2. Create three measures for table visual filter:

 

 

Measure1 = IF(MAX('Table'[Sign Week]) = MAX(Slicer[Week]),1,0)
Measure2 = IF(MAX('Table'[Approval Week]) = MAX(Slicer[Week]),1,0)
Measure3 = IF(MAX('Table'[Start Week]) = MAX(Slicer[Week]),1,0)

 

 

3. Add them to corresponding table visual:

Capture8.PNG

Capture10.PNG

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETXyIIOggoVBlI3dZJ...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

amitchandak
Super User
Super User

@kkassel , if you want all of them to work on the same week. Then you can join all three dates to the same calendar table and have the week number there. Ine relation will be active and others inactive. That you can activate using userelation. 

 

In case you need different weeks then use from the table or have more than one date table

 

@kkassel 

You need to have columns like these in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

This Week Approved= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])), userelation('Date'[Date] , Table[Approve Date])

 

Refer for Userelation and crossfilter https://www.youtube.com/watch?v=e6Y-l_JtCq4

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.