DAX help on category

[Lauraeire_81]

Hi 

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

[AilleryO]

@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

[Anonymous]

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!

[Lauraeire_81]

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

[Anonymous · Accepted solution]

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

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!

[Dangar332]

Hi , @Lauraeire_81 

You can diasable "All selected" feature like below image

[Lauraeire_81]

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

[AilleryO]

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

[Lauraeire_81]

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.