Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors