DAX Calculate sum of rows within two tables with relationship by ID

[M3nbk]

Tables:

1. Incidents table → list of incidents, with fields including Metric ID and State(Closed, In progress, etc).

2. Metrics table → primary key Metric ID, field Value (monthly max allowed).

I want → in the Metrics table, create a calculated column that shows the count of incidents with Status = "Closed" for each Metric ID based on the dates defined in the slicer. (01/01/2025 - 07/31/2025)

Expected result:

In table metrics, there is a unique row with the value of the monthly max allowed ticket.

In table Incidents, there are 5 Incidents resolved, which fall under the parameters

the total debug I want to consolidate the sum of the incidents. The count should be 5, as it is the sum of the debug count(Incidents table), but the result of the formula is 9. It is not taking into consideration the visual filter and it is showing all the data available in the incident table.

VAR TotalDebugCount =

    CALCULATE(

        COUNTROWS(Incidents),

        KEEPFILTERS(Incidents[State] = "Closed"),

        KEEPFILTERS(Incidents[Opened])  

    )

RETURN

TotalDebugCount

The value remains unchanged when I modify the range of the slicer/visual. How can I fix this?

Thanks

[Shahid12523]

Use a measure, not a calculated column, to make slicers work.

ClosedIncidentCount :=
CALCULATE(
COUNTROWS(Incidents),
Incidents[State] = "Closed"
)

This will respect slicers like date range and Metric ID.
If you want to sum debug counts instead of just counting rows:

TotalDebugCount :=
CALCULATE(
SUM(Incidents[DebugCount]),
Incidents[State] = "Closed"
)

[FBergamaschi · Accepted solution]

Hi @M3nbk 

calculated columns are static calculations that never change.

You need a measure in this case, here is the code

CALCULATE(

        COUNTROWS(Incidents),

        Incidents[State] = "Closed"

    )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI