Forum Discussion
Helper IIIDAX help - Get and show multiple values from a column
Hi,
Please help
I Can't seem to work this out, but I would like to show values in a matrix, for IDs based on a chosen category.
Here is my data (2 tables - a dim category table and fact table, joined on category)
What I would like is a matrix showing ALL products and IDs for the chosen category (but not restricted to the chosen category)
| DIM | FACT | |||
| Category | Category | ID | Product | |
| Bikes | Bikes | A | Mountain | |
| Computers | Bikes | A | Training | |
| Cars | Bikes | B | Dirt | |
| Bikes | B | Street | ||
| Computers | A | Laptop | ||
| Computers | B | Server | ||
| Computers | C | Network | ||
| Computers | C | Cables | ||
| Cars | A | Sedan | ||
| Cars | B | Hatch | ||
| Cars | C | Luxury |
For example, if a user chooses category = Bikes,
IDs A and B have purchased Bikes, but I want to show ALL products A and B have purchased... (not just based on the category that was selected) eg
| ID | Product | Product | Product | Product | Product | Product | Product | Product |
| A | Mountain | Training | Laptop | Sedan | ||||
| B | Dirt | Street | Server | Hatch |
So, the result needs to get the IDs based on category chosen, and then show all products for those IDs
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.
7 Replies
- Dangar332
Resident Rockstar
Hi, TrevLc
Try below measureALL Product = var a = VALUES('FACT'[ID]) var b = FILTER(all('FACT'[Product],'FACT'[ID]),'FACT'[ID] in a) var c = CONCATENATEX(b,'FACT'[Product]," , ") RETURN IF(ISINSCOPE('FACT'[ID]),c)
Refer below ImageRegards,
Dangar
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TrevLcThanks Dangar332 It is so close to what I need.
I can't work out what I need to change to make it work.
I need products in the columns, so when nothing is selected, it gives this:
and when something is selected, I need it to give this:
(Bikes is selected, and hence shows all products for IDs A and B)
cath1ynnResolver II
I think this is what you want:
No Category selected:
One Category selected:
You want to remove any link using Category between the 2 tables. You need to create 1 column and 2 measures:
Column
AllCategories = VAR _id = Table2[ID] RETURN CONCATENATEX(FILTER(Table2, Table2[ID] = _id), Table2[Category], "|")Measures
Products = SELECTEDVALUE(Table2[Product])ToFilter = SEARCH(SELECTEDVALUE(Table1[Category]), SELECTEDVALUE(Table2[AllCategories]), 1, 0)For the Matrix visual, I used the ID as the Rows and Product as the Columns. I then used the new Products measure as the Values.
I then used the ToFilter measure as a filter on that visual and set it to filter for values greater than 0.
Given the limited sample set, I couldn't test it fully with multiple Categories selected in the slicer but it should work with that too.
- TrevLc
cath1ynn 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
