Show only if Result is consistent

[Kalachuchi]

 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)

[PaulDBrown · Accepted solution]

@Kalachuchi 

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

[amitchandak]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8