Forum Discussion
Helper IIIDAX help - Get and show multiple values from a column
It's a bit convoluted but there's a way to do it, it's just not something nice.
You said that you need the link between the 2 tables to use it in other visualisation such as count of something. Let's just use Count of ID as an example.
I have created the link between the 2 tables but I have left the link inactive.
Then I created a new measure to count the number of IDs that I can use in this other visualisation
CountID =
CALCULATE(
COUNT(Table2[ID]),
USERELATIONSHIP(Table2[Category], Table1[Category])
)I then set Page 1 (where the matrix table is) as a Drillthrough Page type and added Category from Table1 as a Drillthrough field
Then on a new report page, I created a bar chart visual using the Category field from Table1 and the CountID measure from Table2.
I can then drillthrough from this visual to Page 1
And you will see that the matrix table here has been filtered.
Similarly, I created a new measure to count the number of unique products by category and added a visual for that as well. I then drill through from that visual to the other report page. (I had to add another record to the sample dataset because the sample dataset you gave me managed to have exactly 3 unique products per category 😅)
Anyways, here's the sample PBIX file for you to play with.
Helper IIIcath1ynn Thanks so much. It seems to work, however, I need the link using category for other visualisations on my report.
It is used on another table, giving counts for something else, and I want the user to be able to click on that table value, and use the cross filter function, so when the click on a category, it would be like choosing a value in the slicer in your solution
Resolver IITrevLc You might need to provide a larger sample dataset for us to test with then.
- TrevLc
Thank you.
Here is a larger dataset
Category ID Product International AB Food International AB Canned International AC Food International AD Food International AE Food International AF Food International AG Food International AH Food International AH Packaging Bikes AH Street Bikes AH Dirt Bikes BA Street Bikes BB Street Bikes BC Dirt Bikes BC Mountain Bikes BD Dirt Bikes BE Street Bikes BE Dirt Bikes BF Dirt Bikes BF Dirt Bikes BG Dirt Bikes BH Dirt Bikes BI Dirt Bikes BI Street Bikes BI Mountain Packaging BI Bottles Packaging CB Bottles Packaging CC Bottles Packaging CC Carton Packaging CC Boxes Packaging CD Carton Packaging CD Boxes Packaging CE Bottles Packaging CF Bottles Computers CF Laptop Computers DB Laptop Computers DB Server Computers DB Cables Computers DC Laptop Computers DC Cables Dimension
Category International Bikes Packaging Computers - cath1ynn
It's a bit convoluted but there's a way to do it, it's just not something nice.
You said that you need the link between the 2 tables to use it in other visualisation such as count of something. Let's just use Count of ID as an example.
I have created the link between the 2 tables but I have left the link inactive.
Then I created a new measure to count the number of IDs that I can use in this other visualisation
CountID =
CALCULATE(
COUNT(Table2[ID]),
USERELATIONSHIP(Table2[Category], Table1[Category])
)I then set Page 1 (where the matrix table is) as a Drillthrough Page type and added Category from Table1 as a Drillthrough field
Then on a new report page, I created a bar chart visual using the Category field from Table1 and the CountID measure from Table2.
I can then drillthrough from this visual to Page 1
And you will see that the matrix table here has been filtered.
Similarly, I created a new measure to count the number of unique products by category and added a visual for that as well. I then drill through from that visual to the other report page. (I had to add another record to the sample dataset because the sample dataset you gave me managed to have exactly 3 unique products per category 😅)
Anyways, here's the sample PBIX file for you to play with.