Summarize count of occurrences of particular values from different slicers

[Anonymous]

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 

[jdbuchanan71 · Accepted 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).

[jdbuchanan71 · Accepted solution]

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.

[jdbuchanan71 · Accepted 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).

[TomMartens]

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

[Anonymous]

Edited my query and included the PBIX file

[jdbuchanan71]

I think you can set the link so anyone can download it without granting access.

[Anonymous]

@jdbuchanan71Sorry, edited and set the link to open access now

[jdbuchanan71 · Accepted solution]

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]

@jdbuchanan71 thanks, allow me a little time to test it out and then will mark your answer as solution! Thanks again!!

[parry2k · Accepted solution]

@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....

[parry2k]

@Anonymous do +0 to measure if you want to see 0 instead of BLANK()

[Anonymous]

@jdbuchanan71and @parry2k your solutions are awesome, marked as solutions! thanks a lot!! this community is awesome!!