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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Roym
Helper IV
Helper IV

CountIF value is

I have a table in my dataset that has a column with the Frequency that could be either be Annual, Quarterly, or Periodical. Now I want to count the number of annual issues and Quarterly issues. It should look a bit as the below. 

 

For the annual column I set the code to:

= CALCULATE(COUNTROWS(Table1),Table1[Frequency] = "Annual")
 
That works perfectly but when I add the same for the Quarterly column I get the 'circular dependency' error. I did a lot of Google searched but didn't find the solution (yet). Hopefully anyone can help with this. Thanks!

 

NameFrequencyCount AnnualCount Quarterly
Issue 1Annual1 
Issue 2Quarterly 1
Issue 3Annual1 
1 ACCEPTED SOLUTION


So is it correct that you can only use these measures in your report and not in the dataset?

No. It's possible to write calculated columns that work but you need to be more careful. Please read the articles I linked for more detail.

 

Measure are probably the best choice in this case anyway though. More detail on the difference here:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

View solution in original post

6 REPLIES 6
Roym
Helper IV
Helper IV

found the solutions online, thanks again for all the help!

Roym
Helper IV
Helper IV

Thanks both for the quick replies! Think I got it working, but just to confirm. What I did was:

 

1. Create a new measure for 'Annual Frequency' and 'Quarterly Frequency' with the code from your reply, with the only change between the two the 'annual' and 'quarterly' part.

2. Added these new measures in my report and they work perfectly.

3. When I tried adding them in two seperate columns in my dataset I get the circular dependency error. Does this make sense? So is it correct that you can only use these measures in your report and not in the dataset? I'm relatively new to PowerBI so just want to make sure that what I make, makes sense and is not giving me issues later on 🙂

 

Capture.PNG


So is it correct that you can only use these measures in your report and not in the dataset?

No. It's possible to write calculated columns that work but you need to be more careful. Please read the articles I linked for more detail.

 

Measure are probably the best choice in this case anyway though. More detail on the difference here:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Thanks, read the article and a few others and think that indeed measures make the most sense in this case.

 

One last additional question you can hopefully help me with. The below code as shared before uses one filter. Is it possible to do two in one measure? So count if Table1[Frequency] = "Quarterly" AND Table1[Frequency] = "Annual" ?? Thanks again!!

 

 

Count Quarterly =
CALCULATE (
    COUNT ( Table1[Frequency] ),
    KEEPFILTERS ( Table1[Frequency] = "Quarterly" )
)

 

 

AlexisOlson
Super User
Super User

I'd guess this is because you are using the full table reference in your calculated column, so when you have two calculated columns, each is implicitly referencing the other. Here are a couple of SQLBI articles that should help clarify more fully:

https://www.sqlbi.com/articles/understanding-circular-dependencies/

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

 

 

If you define these as measures (rather than calculated columns), then I don't think you have the same problem but you might need to tweak the code. Try this as a measure:

 

Count Quarterly =
CALCULATE (
    COUNT ( Table1[Frequency] ),
    KEEPFILTERS ( Table1[Frequency] = "Quarterly" )
)

 

parry2k
Super User
Super User

@Roym you should add these as a measure instead of the column and that will do it. To learn more about circular dependency, there is a great article on SQLBI website, just google for it.

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 



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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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