Forum Discussion
Helper IIIShow only if Result is consistent
have a table which has the following data.
| FeedbackProviderName | Week | CustomerService.1 | CustomerService.2 | CustomerService.3 | OverAllRate |
| Jewell Maddox | 1 | Sometimes | Sometimes | Rarely | Sometimes |
| Rolland Gerry | 3 | Usually | Usually | Rarely | Usually |
| Kalyn Tara | 4 | Almost Always | Usually | Usually | Usually |
| Jewell Maddox | 2 | Usually | Usually | Usually | Usually |
| Rolland Gerry | 2 | Usually | Usually | Usually | Usually |
| Kalyn Tara | 2 | Usually | Usually | Usually | Usually |
I want to visualize/show if Feedbackprovider gets two consecutive weeks of same low rating (Usually and sometimes)
Here is one way.
1) Unpivot the service columns in Power Query:
2) create dimension tables for Provider Name, Service and Week and set up the model like this:
3) Create the following measures:
a. a measure to identify low ratings:
Low Rating = CALCULATE ( DISTINCTCOUNT ( FactTable[Rating] ), FactTable[Rating] IN { "Usually", "Sometimes" } )b. a measure to identify if the previous week has a low rating:
Prev Week = CALCULATE ( [Low Rating], FILTER ( ALLEXCEPT ( FactTable, 'Dim Provider Name'[Name], 'Dim Service'[Service] ), FactTable[Week] = MAX ( FactTable[Week] ) - 1 ) )Create a table with the fields form the Dimension tables and add both measures to the "Filters on this visual" in the filter pane and set the values to 1.
If you want to see the ratings for both weeks, use this measure:
Ratings = VAR PrevWeekR = CALCULATE ( MAX ( FactTable[Rating] ), FILTER ( ALL ( FactTable ), FactTable[Week] = MAX ( FactTable[Week] ) - 1 ) ) RETURN IF ( [Prev Week] = 1, MAX ( FactTable[Rating] ) & ", " & PrevWeekR )and you get this (rows show the second week, where the previous week was also rated low):
I´ve attached the PBIX file for your reference
2 Replies
- amitchandak
Super User
Kalachuchi , With help from a week table create measures like
This Week = CALCULATE(Count('Table'[CustomerService.1]), FILTER(ALL('Date'),'Week'[Week]=max('Week'[Week])), filter('Table', 'Table'[CustomerService.1] in {"Usually","Sometimes"}))
Last Week = CALCULATE(Count('Table'[CustomerService.1]), FILTER(ALL('Date'),'Week'[Week]=max('Week'[Week])-1), filter('Table', 'Table'[CustomerService.1] in {"Usually","Sometimes"}))//final measure
Flag = if( not(isblank([This Week])) && not(isblank([Last Week])),1,blank())
In case week across year
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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8 
PaulDBrownCommunity Champion
Here is one way.
1) Unpivot the service columns in Power Query:
2) create dimension tables for Provider Name, Service and Week and set up the model like this:
3) Create the following measures:
a. a measure to identify low ratings:
Low Rating = CALCULATE ( DISTINCTCOUNT ( FactTable[Rating] ), FactTable[Rating] IN { "Usually", "Sometimes" } )b. a measure to identify if the previous week has a low rating:
Prev Week = CALCULATE ( [Low Rating], FILTER ( ALLEXCEPT ( FactTable, 'Dim Provider Name'[Name], 'Dim Service'[Service] ), FactTable[Week] = MAX ( FactTable[Week] ) - 1 ) )Create a table with the fields form the Dimension tables and add both measures to the "Filters on this visual" in the filter pane and set the values to 1.
If you want to see the ratings for both weeks, use this measure:
Ratings = VAR PrevWeekR = CALCULATE ( MAX ( FactTable[Rating] ), FILTER ( ALL ( FactTable ), FactTable[Week] = MAX ( FactTable[Week] ) - 1 ) ) RETURN IF ( [Prev Week] = 1, MAX ( FactTable[Rating] ) & ", " & PrevWeekR )and you get this (rows show the second week, where the previous week was also rated low):
I´ve attached the PBIX file for your reference
