topic Re: Count new value as a single frequency, instead of twice, in the same DAX formula in DAX Commands and Tips

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

Help_me — Wed, 20 May 2026 16:28:48 GMT

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

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

Shai_Karmani — Wed, 20 May 2026 17:19:07 GMT

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

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

oussamahaimoud — Wed, 20 May 2026 18:32:35 GMT

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 🙂

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

Murtaza_Ghafoor — Thu, 21 May 2026 19:28:29 GMT

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()

)

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

ERD — Fri, 22 May 2026 14:18:57 GMT

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] )

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

v-hashadapu — Mon, 25 May 2026 06:45:27 GMT

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.

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

mizan2390 — Tue, 26 May 2026 08:42:39 GMT

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

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

v-hashadapu — Thu, 28 May 2026 10:24:31 GMT

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.

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

carter_gray705 — Tue, 09 Jun 2026 13:52:16 GMT

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.