aggregated sum in same table

[Allav]

Hello community!

I am new here and hoping that you could help me with my question.

I am trying to mark in my data table whether a "Case ID" is open based on the "Open milestones" that it has. i.e. if at least 1 open milestone per Case ID, than the Case ID is open. as you can see in the below excel screenshot, I am using the SUMIF function for that. SUMX wont work since I don't have a criteria to put besides that criteria that I need it to run for same case ID each time.

I need this column to be added either in the same table in the data or ina seperate 1, but I need it on the data level since I have a few more measures which I plan to add based on it.

I hope my question is clear,

thanks in Advance

[Anonymous · Accepted solution]

@Allav You can use ALLEXCEPT

Number of Milestones Per Case Id = CALCULATE(DISTINCTCOUNT(tablename[Milestone ID]), ALLEXCEPT(tablename, tablename[Case ID])

OpenMilestone Flag = 

VAR NumMilestones = CALCULATE(DISTINCTCOUNT(tablename[Milestone ID]), ALLEXCEPT(tablename, tablename[Case ID])

RETURN IF(NumMilestones > 0,1,0)

[Allav]

I just tried the below:

= CALCULATE(min('case data'[Open Milestone])|ALLEXCEPT('case data'|'case data'[Case ID])|IF('case data'[Open Milestone]>0|1|0))

replaced the distinct count by min and the column to refer to the 'open milestone' 1/0 instead of milestone ID

now I get what I wanted

thanks a lot!!

[Allav]

thanks @Anonymous, I used the 2nd formula you suggested like that:

=CALCULATE(DISTINCTCOUNT('case data'[Milestone])|ALLEXCEPT('case data'|'case data'[Case ID])|IF('case data'[Open Milestone]>0|1|0))

And it worked! only thing is that I don't want it to count the number of open milestones, I just want to get 1/0 if there is at least 1 milestone open for the case. I assume I need to replace the 'distinct count' with another function?

[Stachu]

it seems more like you need help on Excel formula rather than DAX or M
do you load this table to PowerPivot and want to do calculations there?
if it's more of an Excel question I suggest going to Technet forum
https://social.technet.microsoft.com/Forums/en-US/home?forum=excel

[Allav]

no, this is not an excel question. I load part of this table to power BI desktop (the other columns are not shown in the screenshot, they contain some dates that indicate if a milestone is open), than I want to make the cokumn of the "Open Case" in the desktop

[Anonymous]

@Allav My solution is in DAX and it will work in Power BI. Also when there are multiple people in the chat it is easier to understand who you are replying to if you put their name.