cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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.
table.pngreport.png
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

 

AlternativeColourOptions.png

 

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

10 REPLIES 10
Highlighted
Super User III
Super User III

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

Highlighted
Super User II
Super User II

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

 

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted

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.

 

wrong.png

Highlighted

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. 

 

 

 

 

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted

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

wrong.png

Highlighted

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

Highlighted

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

 

AlternativeColourOptions.png

 

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted

Hi, im lost. pretty easy for you and not working for me.

i do dimcolour but slicer not work.

please, can you see in pbi ?

https://gofile.io/d/dMuvWw 

Highlighted

@Mareno_123 

 

Use the Measure in the value field. 

Capture.JPG

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors