Forum Discussion

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
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

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.