cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mareno_123 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
AllisonKennedy Solution Sage
Solution Sage

Re: contains selected value dynamic report

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


https://sites.google.com/site/allisonkennedycv

View solution in original post

10 REPLIES 10
nandukrishnavs Community Champion
Community Champion

Re: contains selected value dynamic report

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

AllisonKennedy Solution Sage
Solution Sage

Re: contains selected value dynamic report

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


https://sites.google.com/site/allisonkennedycv

Mareno_123 Helper I
Helper I

Re: contains selected value dynamic report

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

AllisonKennedy Solution Sage
Solution Sage

Re: contains selected value dynamic report

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. 


https://sites.google.com/site/allisonkennedycv

Mareno_123 Helper I
Helper I

Re: contains selected value dynamic report

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

wrong.png

Mareno_123 Helper I
Helper I

Re: contains selected value dynamic report

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

AllisonKennedy Solution Sage
Solution Sage

Re: contains selected value dynamic report

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


https://sites.google.com/site/allisonkennedycv

View solution in original post

Mareno_123 Helper I
Helper I

Re: contains selected value dynamic report

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 

nandukrishnavs Community Champion
Community Champion

Re: contains selected value dynamic report

@Mareno_123 

 

Use the Measure in the value field. 

Capture.JPG

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors