Forum Discussion

Mareno_123's avatar
Mareno_123
Icon for Helper I rankHelper I
6 years ago
Solved

contains selected value dynamic report

Hi,

i have table with ID and colour. Each ID can have more colours. I want report only ID which contains selected colour.

For one colour (blue) i can do "

it contains = IF(CONTAINS(FILTER(ALL('table');'table'[ID]=EARLIER('table'[ID]));'table'[colour];"blue");"is blue";"no blue").
But i dont want do this for all colour. I need select any colour in report.
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago

    Mareno_123  Here is the fully tested solution: 

     

    For this to work, you need a DimColour table that is NOT related to the table. For testing purposes you can create a new TABLE using DAX, but this should ideally be created in M in data model: 

     

    DimColour = VALUES(table[colour])

     

    Create a slicer for DimColour[colour]

     

    Create a DAX MEASURE:  

     

    CountAllColoursforSelectedColour = IF(HASONEVALUE(DimColour[Colour]),CALCULATE(COUNTROWS(FILTER('table','table'[colour]=SELECTEDVALUE('DimColour'[colour]))),all('Table'[Colour])),COUNTROWS('Table'))

     

    Create a matrix using Table[Colour] for columns, Table[ID] for rows and the new measure for values.

     

     

11 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Mareno_123 

     

    Why don't you use colour column in the slicer. So it will show only filtered IDs which contains selected Colour.



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

    • Mareno_123's avatar
      Mareno_123
      Icon for Helper I rankHelper I
      6 years ago

      hi, because i want see other colours on the ID, not only one.

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Mareno_123  If you want it to update based on a Slicer selection, you'll need to create a MEASURE, not column, and refer to the SELECTEDVALUE using DAX; 

     

    Try something similar to this (I haven't tested so might need tweaked slightly):

     

    it contains = IF(CONTAINS(FILTER(ALL('table');'table'[ID]=EARLIER('table'[ID]));'table'[colour];SELECTEDVALUE(colour));"yes";"no").

     

    Then add the 'it contains' measure as a visual level filter to the matrix and set filter to show only for 'yes'. Then create a slicer for colour and see if the matrix updates as you change the selected colour (note it will only work with 1 colour selected, so set selection control on slicer to single select on)

     

    • Mareno_123's avatar
      Mareno_123
      Icon for Helper I rankHelper I
      6 years ago

      Hi, thanks for helping me. Maybe i do something wrong but is it not posiible for me get the table id after earlier.

      Colour after selectedvalue i can pick up from menu like 'table'[colour] but the ID is not automatic showing when i start type.

       

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Yes, sorry you can't use EARLIER in the measure, maybe try a countrows and a calculate: 

        Mareno_123  I have tested it now, so this works if I am understanding your requirements correctly: 

         

        CountAllColoursforSelectedColour = IF(HASONEVALUE(DimColour[Colour]),CALCULATE(COUNTROWS(FILTER('table','table'[colour]=SELECTEDVALUE('DimColour'[colour]))),all('Table'[Colour])),COUNTROWS('Table'))

         

        For this to work, you need a DimColour table that is NOT related to the table. That DimColour table is what you use for the slicer selection. Table[colour] is what you use in the matrix.