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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BI_User_312
Helper I
Helper I

Filtered Card using Sum of Counts

I want to create a card which can be filtered for component count and component type using the example below and return the # of vehicles with that combination. In the card I want to change the count from 1 to 5 and allow for 3 different component types. In the example below a card filtered to gauge and 2 count should return 2 vehicles. A card filtered to switch and 1 count should return 3 vehicles.

 

VehicleComponent TypeComponent Part Number
XXAAAAGaugeRed
XXAAAAGaugeYellow
XXAAAASwitchBlue
XXBBBBGaugeGreen
XXBBBBGaugeSilver
XXBBBBSwitchBlack
XXCCCCSwitchBlue
1 ACCEPTED SOLUTION

@BI_User_312 look at the attached solution, you can choose components, # of components and it will give vehicle count.

 

There are many tables in the attached solution, just focused on cars and components table and what if parameter.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 

 



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.

View solution in original post

11 REPLIES 11
V-pazhen-msft
Community Support
Community Support

@BI_User_312 

I see your question but I think there is something missing in the condition. There is some confusion, because if you select "Gauge" and  “2” count, you can count to the first 2 rows of the sample. The count of Vehicle is only XXAAAA so it is 1. 

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

parry2k
Super User
Super User

@BI_User_312 sorry it is a bit confusing what you are actually looking for and what is the business logic, maybe it is me, could you elaborate bit more.

 



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.

I have a data set which has rows that represent part numbers and the type of components it is. If a vehicle has 5 gauges it will have 5 rows. If it has 3 switches it will have 3 switch rows. I need to get the switch count, gauge count, etc. I understand this but I need a card which I will be able to show for example the total number of vehicles  which have exactly 5 gauges. I will do this for gauges and switches in counts from 1 to 5. I tried to show this in my example table. I was thinking I could create one card  that I could set the filter for desired count and for desired component type. The result is one number showing total number of vehicles with that configuration. Hope that makes sense. @parry2k Thank you

@BI_User_312 I think I know what you are looking for, but to clarify, you will filter on switches or gauges and then another filter like a number, and then you want to show KPI, how many cars are with filter switch or gear and with that number of records.

 

in other words, you want to answer the following:

 

give me a list of cars count with 5 switches

give me a list of cars count with 2 switches 

give me a list of cars count with 10 gauges

and so on...

 

 

 

Am I correct? and it is easily doable once I know this is what you are looking for.

 

 



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.

@parry2k Yes, I believe you understand.

 

@V-pazhen-msft Look again. As parry says, I would set a filter on the KPI for gauges and a count of 2. It would count XXAAA + XXBBBB and results in a visual of 2. Alternatively, I would set a filter on the KPI for switches and count of 1 and it would return XXAAAA+XXBBB+XXCCCC and result in a visual of 3. It is summing all of the vehicles with the configuration I have set up in the filter.

 

Thank you both for your help.

@BI_User_312 look at the attached solution, you can choose components, # of components and it will give vehicle count.

 

There are many tables in the attached solution, just focused on cars and components table and what if parameter.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 

 



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.

@parry2k Additional detail. I think it is double counting. In the attached picture where the filter is set to 2 gauges the number shown should read 1 as it should only count this one vehicle that I am filtered to. The gauge position to the left is set to show gauges that are filtered to a count of 1 so in this example it should be  blank. This vehicle has two gauges so only gauge position 2 should read 1.

 

Gauges.JPG

Also, there is no change in value when componet count is 1 or 0. I  need both.

@BI_User_312 I have no idea what is not working, super lost.



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.

@parry2k 

1. I need to count the number of vehicles which have zero  gauges. Need to subtract the sum of all vehiclse with gauges from total sum of vehicles.

2.  This is setup for component counts. I need vehicle counts. In my example there is a count of one in position 1 and position 2. I expect the result to only show a 1 in position 2.

Capture.JPG

@parry2k Please see attached picture. These vehicles should have a count of two not one. With component count at 1 the table should be blank. With component count at two then  XXAAAA and XXBBB should only be on two rows with a count of two. It doesn't matter that the gauges are different part number. I don't need distinct count, only general count of number if gauges/switches.

 

Gauges.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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