Forum Discussion
Super UserReusable slicer choices logic
See attached PBIX and screen prints which displays the seleted the slicer choices in a handy box.
Click to download PBIX from Onedrive
The standard CONCATENATEX will delimit all the slicer values with commas. For example "A,B,C,D,E".
Whereas my DAX logic displays "A,B,C,D and E" and also "A,B,C,D and 5 others"
It works ok but requires this DAX logic to be copied, pasted and editted for every slicers column.
✂ Customer =
// set an item limit
VAR limit = 9
// get a list upto the limit
VAR customer_list =
TOPN(limit + 1, VALUES('Dim Customer'[Customer]), 'Dim Customer'[Customer])
// get the max item in the list
VAR customer_max =
CALCULATE(
MAX('Dim Customer'[Customer]),
customer_list)
// format the ending text ( for example " and " or " and 5 others"
VAR customer_endtext =
IF(DISTINCTCOUNT('Dim Customer'[Customer]) > limit,
" and " & DISTINCTCOUNT('Dim Customer'[Customer]) - limit & " others",
" and " & MAX('Dim Customer'[Customer])
)
// concatenate the list ( for example A, B, C, D, E )
VAR customer =
IF(ISFILTERED('Dim Customer'[Customer]),
CONCATENATEX(
customer_list,
'Dim Customer'[Customer],
", ",
'Dim Customer'[Customer]
),
"ALL")
RETURN
// replace the last item in the contenations with the ending text (for example "A, B, C, D and E" or "A, B, C, D and 5 others"
"= " & SUBSTITUTE(customer, ", " & customer_max, customer_endtext )
Can any Super Users think of a way to create a reusable DAX function or calculation group that works for any column?
I don't think that it is possible in this scenario, but you might know of a way. Thank you.
No AI generated answers or wild guessing answers.
Please edit and share the PBIX to show propsed solutions work.
Many thanks for helping. 😀😀😀
C.C. danextian
Hi speedramps,
I would personally recommend a DAX user-defined function for this (assuming you're happy to use this feature as it is still in preview).
For example, you could create a function that takes two arguments: a column reference and limit. You would then define measures that each pass the required column reference and limit to this function.
As an example, I have rewritten your measures using the below UDF in the attached PBIX (slightly rewritten using variables for some expressions). The main change is that every reference to the column
'Dim Customer'[Customer](or other column) has been replaced by the function argumentCol. Please check the logic is still correct 🙂( Col: COLUMNREF, Limit: INT64 ) => // get a list upto the Limit VAR col_list = TOPN ( Limit + 1, VALUES ( Col ), Col ) VAR col_count = DISTINCTCOUNT ( Col ) // get the max item in the column VAR col_max = MAX ( Col ) // get the max item in the list of top values VAR col_list_max = MAXX ( col_list, Col ) // format the ending text ( for example " and " or " and 5 others" VAR col_endtext = IF ( col_count > Limit, " and " & col_count - Limit & " others", " and " & col_max ) // concatenate the list ( for example A, B, C, D, E ) VAR col_concat = IF ( ISFILTERED ( Col ), CONCATENATEX ( col_list, Col, ", ", Col ), "ALL" ) RETURN // replace the last item in the concatenations with the ending text (for example "A, B, C, D and E" or "A, B, C, D and 5 others" "= " & SUBSTITUTE ( col_concat, ", " & col_list_max, col_endtext )The
✂ Customermeasure would then be:✂ Customer = ColumnSelectionsString ( 'Dim Customer'[Customer], 9 )Would something like this work for you?
2 Replies
- OwenAuger
Hi speedramps,
I would personally recommend a DAX user-defined function for this (assuming you're happy to use this feature as it is still in preview).
For example, you could create a function that takes two arguments: a column reference and limit. You would then define measures that each pass the required column reference and limit to this function.
As an example, I have rewritten your measures using the below UDF in the attached PBIX (slightly rewritten using variables for some expressions). The main change is that every reference to the column
'Dim Customer'[Customer](or other column) has been replaced by the function argumentCol. Please check the logic is still correct 🙂( Col: COLUMNREF, Limit: INT64 ) => // get a list upto the Limit VAR col_list = TOPN ( Limit + 1, VALUES ( Col ), Col ) VAR col_count = DISTINCTCOUNT ( Col ) // get the max item in the column VAR col_max = MAX ( Col ) // get the max item in the list of top values VAR col_list_max = MAXX ( col_list, Col ) // format the ending text ( for example " and " or " and 5 others" VAR col_endtext = IF ( col_count > Limit, " and " & col_count - Limit & " others", " and " & col_max ) // concatenate the list ( for example A, B, C, D, E ) VAR col_concat = IF ( ISFILTERED ( Col ), CONCATENATEX ( col_list, Col, ", ", Col ), "ALL" ) RETURN // replace the last item in the concatenations with the ending text (for example "A, B, C, D and E" or "A, B, C, D and 5 others" "= " & SUBSTITUTE ( col_concat, ", " & col_list_max, col_endtext )The
✂ Customermeasure would then be:✂ Customer = ColumnSelectionsString ( 'Dim Customer'[Customer], 9 )Would something like this work for you?
speedrampsThank you OwenAuger
That does exactly what I want.
Many thanks for going to the trouble to provide the code script and a PBIX example.
That helped me learn about the new review feature, rather than just give a solution to this problem.
I can think of lots of other application for this wonderful new feature.
Cheers !