Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table called Apps with 3 columns:
ID (Prmary Key)
Year
Type
I would like to create a column chart that groups by year, displays a count, and has filters for Type and Year. The SQL query would look something like this:
SELECT Year, COUNT(*)
FROM Apps
WHERE Year IN ([Year filter selected values])
AND Type IN (Type filter selected values])
GROUP BY Year
The Year filter is a simple drag and drop, but how would the Type filter be implemented? Type can't be in the final data set because it would throw off the count. But if Type is not in the final data set, then how can a page filter be created for it?
Thanks,
Tim
Solved! Go to Solution.
@td48260 not sure what you mean using type on the slicer will throw the count off
just use type and year as a slicer and a simple measure
Count = COUNTROWS ( Table )
or maybe I didn't understand your question, explain it with sample data if that is the case.
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
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.
@td48260 yes this functionality is out of the box, seems like you are overcomplicating it.
Simple question, if you have years on rows, count rows on value, and slicer by type A what do you get? You should get the correct result.
Maybe I'm missing here something obvious.
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.
Yes, I was overcomplicating it! Once I removed the GROUP BY operation and added a Measure COUNTROWS(Table), it works.
Thanks for your patience and help!
Tim
Sorry, I didn't explain it well.
ID | Year | Type |
1 | 2014 | A |
2 | 2015 | B |
3 | 2015 | C |
4 | 2016 | A |
5 | 2016 | B |
6 | 2016 | C |
I want a column chart where the Axis column is Year and the Values column is a count of the rows. There should be filters or slicers for Year and Type. In the example above, if all Years and all Types were selected the chart should reflect this data:
Year Value (count)
2014 1
2015 2
2016 3
If only A is checked in the filter/slicer, then this should be the data:
Year Value (count)
2014 1
2016 1
The order of operations should be
1. Filter table rows by Year and Type filter/slicer values
2. Group the results by Year
3. Calculate Count for each Year
I tried creating measures for
COUNTROWS(Table)
and
calculate(count([value]),table[year]== selectedvalue(table[year]) && table[Type] == selectedvalue(table[type])
In both cases I got syntax errors which I've been unable to resolve.
Thanks for your help.
@td48260 not sure what you mean using type on the slicer will throw the count off
just use type and year as a slicer and a simple measure
Count = COUNTROWS ( Table )
or maybe I didn't understand your question, explain it with sample data if that is the case.
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
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.
= calculate(count([value]),table[year]== selectedvalue(table[year]) && table[Type] == selectedvalue(table[type]))
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!