Dynamic column based on two conditions (matching)

[collis]

Hello all

This forum helped me get a dynamic column working, counting the number of times a date shows (representing an activity) 😍

FreqPerDay_ALL = CALCULATE(count(ActiveDaysCount[DatesActive]),ALLEXCEPT(ActiveDaysCount,ActiveDaysCount[DatesActive]))

Now I need to create a new column specific to each region, counting the date frequency again, but only if "Melbourne" is the Region. I tried an 'if' statement, which resulted in only the Melbourne rows triggering a count. However, the result still counted from all rows, so I don't have the filtering right. Can you assist?

So in the FreqPerDay_MELB column, only the rows where Melbourne is listed trigger a count, and the count only includes dates with Melbourne as the region.

[collis · Accepted solution]

I fixed it ... a small change and its working

- 

[Anonymous]

Dear Collis,

To achieve this, you can add a filter specifically for "Melbourne" in your CALCULATE function. This will limit the count to only rows where the region is "Melbourne". You can achieve this by using a filter within the CALCULATE function. Here’s how to modify your formula:

FreqPerDay_MELB =
CALCULATE(
COUNT(ActiveDaysCount[DatesActive]),
ALLEXCEPT(ActiveDaysCount, ActiveDaysCount[DatesActive]),
ActiveDaysCount[Region] = "Melbourne"
)

Note:

The ALLEXCEPT function clears all filters except for the date column, allowing the formula to count all instances of each date, but only within the context of any specified filters, like the region. The filter ActiveDaysCount[Region] = "Melbourne" then ensures that the calculation is restricted to rows where the region is specifically "Melbourne." This setup will make the FreqPerDay_MELB column count only the dates associated with "Melbourne."

[collis]

Thanks - its not quite working, but nearly there!
Looking at the below. I think it's calculating the correct number of activities on a date in Melbourne e.g. 2 on 1/02/2024, as shown below, but it's listing the two in every row for that date, instead of just the Melbourne lines. The other rows for that date that aren't in Melbourne need to be blank. 

I really appreciate your post.