Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

@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

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

11 REPLIES 11
AllisonKennedy
Super User
Super User

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

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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

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. 

 

 

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

I missed a bar after the first table and after adding it is still bad 😞

wrong.png

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 

@Mareno_123 

 

Use the Measure in the value field. 

Capture.JPG


Regards,
Nandu Krishna

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

wrong.png

@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

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

thank you so so much 

nandukrishnavs
Super User
Super User

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


Regards,
Nandu Krishna

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.