Forum Discussion

collis's avatar
collis
Frequent Visitor
1 year ago
Solved

Dynamic column based on two conditions (matching)

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[DatesAct...
Need Help
  • collis's avatar
    collis
    1 year ago

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

     

    - 

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    collis
    Frequent Visitor
    1 year ago

    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.