Adding an IF to a CALCULATE measure

[tstraker]

Hi All, 

I have a simple measure that calculates the number of closed stories that are related to my work items (in Azure Devops).

M related count closed = CALCULATE(
    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),
    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"
)

The results work fine for anything that actually has any closed related items. However if there are none (no closed items returned) then I simply want to render the value for that row as '0'.

Can I can add a default value or an IF statement to the end of my calculate in some way?

The loose logic would be as follows:

M related count closed = CALCULATE(
    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),
    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"
) .... IF count returns null or 0 then set value to '0'

[parry2k · Accepted solution]

@tstraker I would recommend to add + 0 to your original measure which is working and you are good

M related count closed = CALCULATE(
    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),
    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"
) + 0

[tstraker]

Thanks @parry2k - I went with this solution as its the lowest code.

@ChrisMendoza- Your solution also worked, I'm not sure how to represent that, can we accept multiple replies as a solution? I also learn't about SWITCH form your input so that was a bonus to!

thanks everyone.

[Anonymous]

Try:

If(IsBlank(WorkItemLinks[TargetWorkItem.State]), 0, CALCULATE(
    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),
    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"
))

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

[tstraker]

Thanks, I see the logic but the following error occurs.

A single value for column 'TargetWorkItem.State' in table 'WorkItemLinks' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
"Closed" is one of several values for the State column. Perhaps we could say <> to "Closed"?

I tried:

M related count closed = If(WorkItemLinks[TargetWorkItem.State] <> "Closed"), 0, CALCULATE(

    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),

    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"

))

But then got this error (so I'm clearly doing that wrong 😉 😞

The syntax for ',' is incorrect. (DAX(If(WorkItemLinks[TargetWorkItem.State] <> "Closed"), 0, CALCULATE( DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]), 'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed")))).

[Ashish_Mathur]

Hi,

Remove the ) after "Closed"

[ChrisMendoza]

@tstraker -

Have you tried a SWITCH statement instead? Also in your last reply...

---

@tstraker wrote:

Thanks, I see the logic but the following error occurs.

A single value for column 'TargetWorkItem.State' in table 'WorkItemLinks' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
"Closed" is one of several values for the State column. Perhaps we could say <> to "Closed"?

I tried:

M related count closed = If(WorkItemLinks[TargetWorkItem.State] <> "Closed"), 0, CALCULATE(

    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),

    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"

))

But then got this error (so I'm clearly doing that wrong 😉 😞

The syntax for ',' is incorrect. (DAX(If(WorkItemLinks[TargetWorkItem.State] <> "Closed"), 0, CALCULATE( DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]), 'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed")))).

---

You closed your IF right after If(WorkItemLinks[TargetWorkItem.State] <> "Closed"). That is my guess as to your error message.

[tstraker]

Hi @ChrisMendoza 

I like the idea of a SWITCH, however I need help in structuring it.

I've started off by placing my calculate measure into a VAR.

VAR countVar = CALCULATE(
    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),
    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"
)

Then I want to be able to execute my SWITCH (need help here)

VAR countVar= CALCULATE(
    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),
    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"
)

Return SWITCH(countVar = BLANK(), True, 0, Countvar)

In the switch, I'm attempting to say:

If countVar = nothing (Blank) then set the value to 0, otherwise use the countVar value.

[ChrisMendoza]

@tstraker -

It would be more like:

SWITCH (
  TRUE ( ),
  countVar = 0, 0,
  countVar
)

You can read more about it at https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/.

 If you had supplied a data sample, we could help you more effectively.