Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tstraker
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(
    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'


1 ACCEPTED SOLUTION
parry2k
Super User
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(
    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),
    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"
) + 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.

View solution in original post

8 REPLIES 8
parry2k
Super User
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(
    DISTINCTCOUNT ( 'WorkItemLinks'[TargetWorkItemId]),
    'WorkItemLinks'[LinkTypeName] = "related", WorkItemLinks[TargetWorkItem.State] = "Closed"
) + 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.

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:

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."

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")))).

Hi,

Remove the ) after "Closed"


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.

@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.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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