cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Adding an IF to a CALCULATE measure

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

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(
) .... IF count returns null or 0 then set value to '0'

1 ACCEPTED SOLUTION
Super User

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

``````M related count closed = CALCULATE(
) + 0``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

8 REPLIES 8
Super User

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

``````M related count closed = CALCULATE(
) + 0``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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
Not applicable

Try:

))

---

Frequent Visitor

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

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

Super User

Hi,

Remove the ) after "Closed"

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resident Rockstar

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

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

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Frequent Visitor

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

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

VAR countVar= CALCULATE(
)

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.

Resident Rockstar

It would be more like:

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors