Measure to show count of items after another column date

[CSJ123]

Hi

I wonder if someone can help me with this as I have been working on it for a while with limited success!!

I am trying to write a measure to show how many courses an instructor has taught since their last assessment. 

My columns are:

'Table1' [Last Instructor Check]

'Table2' [Total Courses] (a measured column combining 2 types of courses so not sure if table name needed)

The complication for me is that using this measure I need to create conditioning based on the [Instructor Risk Level] (contains values H, M, L) 

• IF an instructor is Low Risk [Instructor Risk Level]= L, the column background should be green until that instructor has taught 6 courses since [Last Instructor Check], then change to red
• IF an instructor is Medium Risk [Instructor Risk Level]= M, the column background should be green until that instructor has taught 3 courses since [Last Instructor Check], then change to red
• IF an instructor is High Risk [Instructor Risk Level]= H, the column background should always be red.

Is this achievable in PowerBi?

Many thanks 🙂

[v-rongtiep-msft · Accepted solution]

Hi @CSJ123 ,

Please hvae a try.

Create a measure.

measure =
SWITCH (
    TRUE (),
    [Instructor Risk Level] = "L"
        && [Last Instructor Check] < 6, "Green",
    [Instructor Risk Level] = "L"
        && [Last Instructor Check] = 6, "Red",
    [Instructor Risk Level] = "M"
        && [Last Instructor Check] < 3, "Green",
    [Instructor Risk Level] = "M"
        && [Last Instructor Check] = 3, "Red",
    [Instructor Risk Level] = "H", "Red",
    BLANK ()
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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