Counting the number of rows equal to a condition from a calendar

[Michalison]

I have created a calendar table (TrendDate) using this formula  

I also have a table called Trend Analysis with this info (there are numerous rows for each course/person the below is a reocrd of 1 persons rows

What I want to do is create a  visual  so that if I pick a date from the calendar i.e. 28/01/2024 it will tell me how many courses where equal to non compliant on that particular day  I have tried numerous ways but got nowhere any suggestions would be good  (ps I do have a relationship 1 to many from the calendar table to the trend analysis table

[Anonymous · Accepted solution]

Hi All,

Firstly,  @daircom thanks for the solution!

And @Michalison according to my understanding,  I want to share another method.

For your question, I'll try to reproduce your data for manipulation and filter for Not compliant, hope it helps.

Count_Not_Compliant = 
CALCULATE(
    COUNTROWS('Trend_Analysis_Data'),
    FILTER(
        'Trend_Analysis_Data',
        'Trend_Analysis_Data'[Compliant Y/N] = "Not compliant" &&
        'Trend_Analysis_Data'[Compliance End Date] >= MIN('TrendDate'[Date]) &&
        'Trend_Analysis_Data'[Compliance End Date] <= MAX('TrendDate'[Date])
    )
)

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[daircom]

@Michalison ,
You could create a slicer and move the date column of your calendar to it.

Then create the followng measure the following formula:

measure = CALCULATE(COUNT(table[Compliance Y/N]), FILTER(table, table[Complance Y/N] = "Not compliant"))

then also show this measure in a visual (you still have to replace "table" with your actual table in the DAX formula)