Forum Discussion

Lauraeire_81's avatar
Lauraeire_81
Helper I
1 year ago
Solved

DAX help on category

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. 

CountriesFood
AmericaHot dogs
AmericaCorn dogs
AmericaBigmacs
Ireland Stew
Ireland Cabbage and bacon
  • Anonymous's avatar
    Anonymous
    1 year ago

    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!

8 Replies

  • AilleryO's avatar
    AilleryO
    Memorable Member
    1 year ago

    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's avatar
      Lauraeire_81
      Helper I
      1 year ago
       
      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. 
       
       
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Lauraeire_81
      Helper I
      1 year ago

      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

  • AilleryO's avatar
    AilleryO
    Memorable Member
    1 year ago

    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