topic Re: DAX help on category in DAX Commands and Tips

DAX help on category

Lauraeire_81 — Mon, 10 Mar 2025 15:54:40 GMT

I am looking to display the Countries listed depending on what food items are selected in a slicer (where multiple options can be selected).

Say 'Hot Dogs', 'Corn dogs' and 'Stew' are selected, I want a measure to say something like this:

'Corn Dogs' and 'Hot dogs' found in 'America'. Count of 2.

'Stew' is found in Ireland. Count of 1.

Is there any way to search on a table like this and display the items in a similar format?

Thanks in advance,

Laura

See table 'CountryFood' below.

Countries	Food
America	Hot dogs
America	Corn dogs
America	Bigmacs
Ireland	Stew
Ireland	Cabbage and bacon

Re: DAX help on category

AilleryO — Mon, 10 Mar 2025 16:03:45 GMT

Hi,

We need more details to really help you but basically, if you want to get the selectedvalue :

Get Category List (selected ones) =
VAR LstCat = DISTINCT( Products[Category] )
VAR NbCat = COUNTROWS( LstCat )
RETURN
SWITCH( TRUE() ,
 NOT ISFILTERED( Products[Category] ) , "All cat displayed" ,
 NbCat > 10    , "Too many cat selected (" & NbCat & " categories selected)" ,
 ISFILTERED( Products[Category] ) ,  CONCATENATEX( LstCat , Products[Category] , ", ")   )

In this formula, I test the number of category selected, not to display the result if there are too many (more than 10). So you can adapt that part for the count you required.

Let us know if you need more details

Re: DAX help on category

Lauraeire_81 — Mon, 10 Mar 2025 17:24:14 GMT

AilleryO Thank you - this works.

Would it be possible to add a value if the 'Select all' value in the slicer was selected? I have 9 values and sometimes I would just want to select all of them. Thank you.

Re: DAX help on category

Anonymous — Tue, 11 Mar 2025 02:09:09 GMT

Hi Lauraeire_81 ,

Thanks for the reply from AilleryO .

I tested it using your example data.

Please create a measure:

Count = VAR TotalFoods = CALCULATE( DISTINCTCOUNT('Table'[Food]), ALL('Table') ) VAR SelectedFoods = DISTINCTCOUNT('Table'[Food]) VAR IsSelectAll = SelectedFoods = TotalFoods VAR SummaryTable = SUMMARIZE( 'Table', 'Table'[Countries], "FoodList", CONCATENATEX( VALUES('Table'[Food]), 'Table'[Food], " and " ), "FoodCount", COUNTROWS( 'Table' ) ) VAR ResultString = CONCATENATEX( SummaryTable, "'" & [FoodList] & "' found in '" & [Countries] & "'. Count of " & FORMAT([FoodCount], "0"), UNICHAR(10) ) RETURN IF( IsSelectAll, "Select all: " & ResultString, ResultString )

The final visual effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: DAX help on category

Lauraeire_81 — Tue, 11 Mar 2025 12:24:43 GMT

hi Anonymous - Thanks for the above DAX, it's really helpful. Can you include the text 'No values selected' if either 'Select all' or any of the other items below it are not selected. It seems to Select all by default.

Thanks

Laura

Re: DAX help on category

Dangar332 — Tue, 11 Mar 2025 13:42:02 GMT

Hi , Lauraeire_81

You can diasable "All selected" feature like below image

Re: DAX help on category

Lauraeire_81 — Tue, 11 Mar 2025 14:23:08 GMT

Hi Dangar332 I want to have the Select all option in the slicer if possible. I don't want to display everything if nothing is selected. does that make sense? I would prefer something like 'Please select an option'. Thanks, Laura

Re: DAX help on category

Anonymous — Fri, 14 Mar 2025 03:01:58 GMT

Hi Lauraeire_81 ,

Use the following DAX to create a new table:

Table 2 = VALUES('Table'[Countries])

Create a relationship between the two tables:

Create a measure:

Measure = VAR _currentCT = SELECTEDVALUE ( 'Table 2'[Countries] ) VAR _values = VALUES ( 'Table'[Countries] ) RETURN IF ( SELECTEDVALUE ( 'Table 2'[Countries] ) IN _values && ISFILTERED ( 'Table'[Countries] ), "'" & CONCATENATEX ( FILTER ( 'Table', 'Table'[Countries] = _currentCT ), 'Table'[Food], " and " ) & "' found in '" & _currentCT & ". " & "Count of " & COUNTROWS ( FILTER ( 'Table', 'Table'[Countries] = _currentCT ) ), IF ( NOT ISFILTERED ( 'Table'[Countries] ), "'Please select an option'" ) )

Drag the fields of Table2 to the table visual object, the page effect is as follows:

I have tested it and found that select all and select no object is one case, to show all cases when select all. I have a workaround: I can drag the “Table” table's countries field and count measure to the table's visual object, save the slicer select all case as a bookmark, and insert a button. Insert a button and set the action of the button to this bookmark.

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

Re: DAX help on category

AilleryO — Fri, 28 Mar 2025 13:27:04 GMT

Lauraeire_81 sorry for the late reply, but yes it could be possible. As I do not know how to detect the all select value, I could suggest to test if the NbCat (in my formula) is equal to the number of possible values :

IF( NbCat = CALCULATE( DISTINCT( Products[Category] ) , ALL(Product) ) ,"All Selected" )

It should do the trick.

Let us know