Forum Discussion
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:
| Name | Frequency | Count Annual | Count Quarterly |
| Issue 1 | Annual | 1 | |
| Issue 2 | Quarterly | 1 | |
| Issue 3 | Annual | 1 |
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/
6 Replies
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.⚡
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" ) )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 🙂
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" ) )
found the solutions online, thanks again for all the help!
