Forum Discussion

QQQ's avatar
QQQ
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Use DISTINCT as filter when calculating SUM

Here is a table

 

Incident_IDTriggersStatus
754571441Red
754571441Green
754588316Purple
754980015Red
754980015Green
754980015Orange

 

Given, there will alway be same number of triggers for a particular ID. But I don't want to count those triggers multiple times.

 

I tried the following DAX. My expected answer is 72, but I get 143 instead. How to fix?

 

Distinct_Matches:=CALCULATE(sum(Incidents[Triggers]),DISTINCT(Incidents[Incident_ID]))

 

Need Help
  • marcorusso's avatar
    marcorusso
    9 years ago

    If you are sure that Triggers and Incident_ID have a 1:1 relatioship, you could use this:

     

    Distinct_Matches :=
SUMX (
    SUMMARIZE ( Incidents, Incidents[Incident_ID], Incidents[Triggers] ),
    Incidents[Triggers]
)

2 Replies

  • marcorusso's avatar
    marcorusso
    Icon for Most Valuable Professional rankMost Valuable Professional
    9 years ago

    If you are sure that Triggers and Incident_ID have a 1:1 relatioship, you could use this:

     

    Distinct_Matches :=
SUMX (
    SUMMARIZE ( Incidents, Incidents[Incident_ID], Incidents[Triggers] ),
    Incidents[Triggers]
)