Forum Discussion

Mareno_123's avatar
Mareno_123
Helper 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('ta...
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.

     

     

AllisonKennedy's avatar
AllisonKennedy
Community 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
    Helper 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
      Community 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. 

       

       

       

       

      • Mareno_123's avatar
        Mareno_123
        Helper I
        6 years ago

        i missed one bar after first table and after add it it still wrong 😞