Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 "
Solved! Go to 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:
Create a matrix using Table[Colour] for columns, Table[ID] for rows and the new measure for values.
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
@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)
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.
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:
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.
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 😞
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 ?
i missed one bar after first table and after add it it still wrong 😞
@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:
Create a matrix using Table[Colour] for columns, Table[ID] for rows and the new measure for values.
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
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 🙂
hi, because i want see other colours on the ID, not only one.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |