Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 |
Solved! Go to 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/
found the solutions online, thanks again for all the help!
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" )
)
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" )
)
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
89 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |