Count new value as a single frequency, instead of twice, in the same DAX formula

[Help_me]

I have a table (actions_taken) with columns called project_number, action_effective_dt, and action_name. All these values can and do repeat. Each project number can repeat & have multiple action names associated with it & multiple action effective dates. One of you helped me write the DAX formula below (thank you!), but I now want to modify this formula to count the “IE/FE” as a single occurrence. Currently the “IE/FE” is being double counted.

DAX Formula:

ClosingsAction =

VAR CurrentProject = actions_taken[project_number]

VAR CurrentDate = actions_taken[action_effective_dt]

VAR HasA =

    CALCULATE(

        COUNTROWS(actions_taken),

        ALL(actions_taken), -- Overrides context transition to scan the whole table

       actions_taken[project_number] = CurrentProject,

       actions_taken[action_effective_dt] = CurrentDate,

       actions_taken[action_name] = " Initially Endorsed"

    ) > 0

VAR HasB =

    CALCULATE(

        COUNTROWS(actions_taken),

        ALL(actions_taken), -- Overrides context transition to scan the whole table

       actions_taken[project_number] = CurrentProject,

       actions_taken[action_effective_dt] = CurrentDate,

       actions_taken[action_name] = "Finally Endorsed"

    ) > 0

RETURN

    SWITCH(

        TRUE(),

        -- If both A and B exist on same date for the same project

       actions_taken[action_name] IN {"Initially Endorsed","Finally Endorsed"} && HasA && HasB, "IE/FE",

        -- Individual mappings

       actions_taken[action_name] = "Initially Endorsed", "IE",

       actions_taken[action_name] = "Finally Endorsed", "FE",

       actions_taken[action_name] = "Firm Application Withdrawn", "W",

       actions_taken[action_name] = "Firm Commitment Expired", "E",

        BLANK()

    )

Sample data:

Project_number	Action_effective_dt	Action_name
Ghj123	5/20/2026	Initially Endorsed
Ghj123	5/20/2026	Finally Endorsed
Hop963	4/8/2026	Firm Commitment Expired
Lty789	3/7/2026	Firm Application Withdrawn
Dsw493	3/7/2026	Firm Commitment Expired
Kjh259	3/5/2026	Initially Endorsed

Table outcomes with reclassified categories from “ClosingsAction” column:

Action_Name	Frequency
IE/FE	2 (I want this to be counted as a single action/ occurrence)
E	2
W	1
IE	1

[carter_gray705]

Count distinct Project Number + Effective Date combinations instead of counting rows. This will treat IE/FE on the same project/date as a single occurrence and prevent double counting.

[v-hashadapu]

Hi @Help_me , 

Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[mizan2390]

@Help_me 

Use this code for the ClosingsAction column. By using ALLEXCEPT, it natively groups by project without forcing a heavy iteration over the entire table for every row.

ClosingsAction =
VAR _t =
    CALCULATETABLE (
        VALUES ( actions_taken[action_name] ),
        ALLEXCEPT ( actions_taken, actions_taken[project_number], actions_taken[action_effective_dt] )
    )
RETURN
    SWITCH (
        TRUE (),
        "Initially Endorsed" IN _t && "Finally Endorsed" IN _t, "IE/FE",
        "Initially Endorsed" IN _t, "IE",
        "Finally Endorsed" IN _t, "FE",
        "Firm Application Withdrawn" IN _t, "W",
        "Firm Commitment Expired" IN _t, "E",
        BLANK ()
)

Create this explicit measure to use in your visuals instead of dragging the column to the "Values" well. This dynamically groups the data in the formula engine before counting it, ensuring "IE/FE" is only counted once per project/date combination.

Distinct Action Count =
COUNTROWS (
    SUMMARIZE (
        actions_taken,
        actions_taken[project_number],
        actions_taken[action_effective_dt],
        actions_taken[ClosingsAction]
    )
)

if this solves your problem, please mark this as solution and give a kudos.

@me so that i dont loose the thread

[v-hashadapu]

Hi @Help_me , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by @Shai_Karmani  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.

Thank you @Shai_Karmani  for your valuable response.

[ERD]

Hello @Help_me,

I would use a simpler and shorter code for a ClosingsAction calculated column (I suppose it is a calculated column in your table):

ClosingsAction =
VAR _t =
    CALCULATETABLE (
        VALUES ( actions_taken[Action_name] ),
        ALLEXCEPT ( actions_taken, actions_taken[Project_number] )
    )
RETURN
    SWITCH (
        TRUE (),
        "Initially Endorsed" IN _t && "Finally Endorsed" IN _t, "IE/FE",
        "Initially Endorsed" IN _t, "IE",
        "Finally Endorsed" IN _t, "FE",
        "Firm Application Withdrawn" IN _t, "W",
        "Firm Commitment Expired" IN _t, "E",
        BLANK ()
    )

Then you can use a simple measure to count projects:

# of projects = COUNT( actions_taken[Project_number] )

[Murtaza_Ghafoor]

@Help_me 

Try this DAX measure, it will supress the second row. Thanks

ClosingsAction. =

VAR CurrentProject = actions_taken[project_number]

VAR CurrentDate = actions_taken[action_effective_dt]

VAR CurrentAction = actions_taken[action_name]




VAR HasIE =

    CALCULATE(

        COUNTROWS(actions_taken),

        ALL(actions_taken),

        actions_taken[project_number] = CurrentProject,

        actions_taken[action_effective_dt] = CurrentDate,

        actions_taken[action_name] = "Initially Endorsed"

    ) > 0




VAR HasFE =

    CALCULATE(

        COUNTROWS(actions_taken),

        ALL(actions_taken),

        actions_taken[project_number] = CurrentProject,

        actions_taken[action_effective_dt] = CurrentDate,

        actions_taken[action_name] = "Finally Endorsed"

    ) > 0




RETURN

SWITCH(

    TRUE(),




    -- Return IE/FE only ONCE

    HasIE && HasFE && CurrentAction = "Initially Endorsed", "IE/FE",




    -- Suppress second row

    HasIE && HasFE && CurrentAction = "Finally Endorsed", BLANK(),




    -- Individual mappings

    CurrentAction = "Initially Endorsed", "IE",

    CurrentAction = "Finally Endorsed", "FE",

    CurrentAction = "Firm Application Withdrawn", "W",

    CurrentAction = "Firm Commitment Expired", "E",




    BLANK()

)

[oussamahaimoud]

Hi @Help_me,

When both "Initially Endorsed" and "Finally Endorsed" exist on the same date/project, both rows get labeled "IE/FE", so COUNTROWS or any frequency count sees 2 rows instead of 1.

So you ssuppress the "Finally Endorsed" row when it's part of an IE/FE pair, so only one row represents the combined action.

ClosingsAction =

VAR CurrentProject = actions_taken[project_number]

VAR CurrentDate = actions_taken[action_effective_dt]

VAR CurrentAction = actions_taken[action_name]

VAR HasIE =

    CALCULATE(

        COUNTROWS(actions_taken),

        ALL(actions_taken),

        actions_taken[project_number] = CurrentProject,

        actions_taken[action_effective_dt] = CurrentDate,

        actions_taken[action_name] = "Initially Endorsed"

    ) > 0

VAR HasFE =

    CALCULATE(

        COUNTROWS(actions_taken),

        ALL(actions_taken),

        actions_taken[project_number] = CurrentProject,

        actions_taken[action_effective_dt] = CurrentDate,

        actions_taken[action_name] = "Finally Endorsed"

    ) > 0

VAR IsPair = HasIE && HasFE

RETURN

    SWITCH(

        TRUE(),

        -- IE/FE pair: only the IE row carries the label; FE row is suppressed

        CurrentAction = "Initially Endorsed" && IsPair, "IE/FE",

        CurrentAction = "Finally Endorsed" && IsPair, BLANK(), -- ← suppressed

        -- Individual mappings (only reached when NOT part of a pair)

        CurrentAction = "Initially Endorsed", "IE",

        CurrentAction = "Finally Endorsed", "FE",

        CurrentAction = "Firm Application Withdrawn", "W",

        CurrentAction = "Firm Commitment Expired", "E",

        BLANK()

    )

Option: if you're counting in a measure rather than relying on the calculated column directly, use this to exclude the blank rows:

ActionFrequency =

CALCULATE(

    COUNTROWS(actions_taken),

    actions_taken[ClosingsAction] <> BLANK()

)

Hope this helps! Don't forget to mark as solution and thumbs up in order to motivate me to keep helping 🙂 

[Shai_Karmani · Accepted solution]

The issue is that your calculated column produces "IE/FE" on both rows of the IE and FE pair, so an implicit row count naturally returns 2. Instead of counting rows, count the distinct project + date combinations within each ClosingsAction.

Add this measure and use it in the visual:

Action Count =
COUNTROWS(
    SUMMARIZE(
        actions_taken,
        actions_taken[project_number],
        actions_taken[action_effective_dt],
        actions_taken[ClosingsAction]
    )
)

For Ghj123 on 5/20/2026 the two source rows collapse into one combination, so IE/FE returns 1 while E, W, and IE keep their correct counts.

If this helped, a thumbs up and accepting the solution would be appreciated.

Best,
Shai Karmani