Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
This is my first post! 😊
I have 6 slicers as shown below, the values (1,2,3,4) come from GENERATESERIES functions for each individual slicer. The selected values are reflected on the cards on the left hand side.
I want to count the number of occurences of 1,2,3,4 dynamically and apply these counts in another formula.
Summarizing in a table or calculated column is not possible since slicers cannot affect calculated columns or tables.
Kindly help!!!
PBIX: https://drive.google.com/file/d/1hHfVl8NGiKZDoWRpJQX3AdVQLsaZgdxb/view?usp=sharing
@Greg_Deckler @amitchandak @parry2k @Zubair_Muhammad @Seth_C_Bauer @ImkeF @Sean @MFelix @Ashish_Mathur @GilbertQ @TomMartens @MarcelBeug @Vvelarde @mahoneypat @jdbuchanan71
Solved! Go to Solution.
@Anonymous
Not sure what the table names are for the 6 series but something like this should work.
1 Count =
VAR _Item = 1
VAR _1 = COUNTROWS ( FILTER ( 't1', 't1'[Value] = _Item ) )
VAR _2 = COUNTROWS ( FILTER ( 't2', 't2'[Value] = _Item ) )
VAR _3 = COUNTROWS ( FILTER ( 't3', 't3'[Value] = _Item ) )
VAR _4 = COUNTROWS ( FILTER ( 't4', 't4'[Value] = _Item ) )
VAR _5 = COUNTROWS ( FILTER ( 't5', 't5'[Value] = _Item ) )
VAR _6 = COUNTROWS ( FILTER ( 't6', 't6'[Value] = _Item ) )
RETURN _1 + _2 + _3 + _4 + _5 + _6
You would need this measure 4 times, once for each options (1, 2, 3, 4).
Yep, the measure I gave you will work, I added a +0 to the end so that it will show 0 instead of (Blank). I added the measures to your file and attached it.
@Anonymous this is what I will do, add measure for each value like this.
Count Value 1 =
VAR __tbl =
UNION (
{ [Parameter1 Value] },
{ [Parameter2 Value] },
{ [Parameter3 Value] },
{ [Parameter4 Value] },
{ [Parameter5 Value] },
{ [Parameter6 Value] }
)
RETURN
COUNTX ( FILTER ( __tbl, [Value] = 1 ), 1 )
Count Value 2 =
VAR __tbl =
UNION (
{ [Parameter1 Value] },
{ [Parameter2 Value] },
{ [Parameter3 Value] },
{ [Parameter4 Value] },
{ [Parameter5 Value] },
{ [Parameter6 Value] }
)
RETURN
COUNTX ( FILTER ( __tbl, [Value] = 2 ), 1 )
Count Value 3 =
VAR __tbl =
UNION (
{ [Parameter1 Value] },
{ [Parameter2 Value] },
{ [Parameter3 Value] },
{ [Parameter4 Value] },
{ [Parameter5 Value] },
{ [Parameter6 Value] }
)
RETURN
COUNTX ( FILTER ( __tbl, [Value] = 3 ), 1 )
and so on....
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.
@Anonymous
Not sure what the table names are for the 6 series but something like this should work.
1 Count =
VAR _Item = 1
VAR _1 = COUNTROWS ( FILTER ( 't1', 't1'[Value] = _Item ) )
VAR _2 = COUNTROWS ( FILTER ( 't2', 't2'[Value] = _Item ) )
VAR _3 = COUNTROWS ( FILTER ( 't3', 't3'[Value] = _Item ) )
VAR _4 = COUNTROWS ( FILTER ( 't4', 't4'[Value] = _Item ) )
VAR _5 = COUNTROWS ( FILTER ( 't5', 't5'[Value] = _Item ) )
VAR _6 = COUNTROWS ( FILTER ( 't6', 't6'[Value] = _Item ) )
RETURN _1 + _2 + _3 + _4 + _5 + _6
You would need this measure 4 times, once for each options (1, 2, 3, 4).
Hey @Anonymous ,
Thanks for the shoutout, please honor my time by providing a pbix that contains sample data, upload the pbix to onedrive or dropbox, and share the link. if you are using an xlsx file to create the sample data share the xlsx as well.
Regards,
Tom
Edited my query and included the PBIX file
I think you can set the link so anyone can download it without granting access.
@jdbuchanan71 thanks, allow me a little time to test it out and then will mark your answer as solution! Thanks again!!
@Anonymous this is what I will do, add measure for each value like this.
Count Value 1 =
VAR __tbl =
UNION (
{ [Parameter1 Value] },
{ [Parameter2 Value] },
{ [Parameter3 Value] },
{ [Parameter4 Value] },
{ [Parameter5 Value] },
{ [Parameter6 Value] }
)
RETURN
COUNTX ( FILTER ( __tbl, [Value] = 1 ), 1 )
Count Value 2 =
VAR __tbl =
UNION (
{ [Parameter1 Value] },
{ [Parameter2 Value] },
{ [Parameter3 Value] },
{ [Parameter4 Value] },
{ [Parameter5 Value] },
{ [Parameter6 Value] }
)
RETURN
COUNTX ( FILTER ( __tbl, [Value] = 2 ), 1 )
Count Value 3 =
VAR __tbl =
UNION (
{ [Parameter1 Value] },
{ [Parameter2 Value] },
{ [Parameter3 Value] },
{ [Parameter4 Value] },
{ [Parameter5 Value] },
{ [Parameter6 Value] }
)
RETURN
COUNTX ( FILTER ( __tbl, [Value] = 3 ), 1 )
and so on....
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.
@Anonymous do +0 to measure if you want to see 0 instead of BLANK()
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.
@jdbuchanan71and @parry2k your solutions are awesome, marked as solutions! thanks a lot!! this community is awesome!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
145 | |
108 | |
107 | |
90 | |
65 |