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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
TrevLc
Helper III
Helper III

DAX 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 CategoryIDProduct
Bikes BikesAMountain
Computers BikesATraining
Cars BikesBDirt
  BikesBStreet
  ComputersALaptop
  ComputersBServer
  ComputersCNetwork
  ComputersCCables
  CarsASedan
  CarsBHatch
  CarsCLuxury

 

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

IDProductProductProductProductProductProductProductProduct
AMountainTraining  Laptop Sedan 
B  DirtStreet Server Hatch

 

So, the result needs to get the IDs based on category chosen, and then show all products for those IDs

1 ACCEPTED SOLUTION

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.

cath1ynn_0-1720674999362.png

 

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

cath1ynn_1-1720675153277.png

 

Then on a new report page, I created a bar chart visual using the Category field from Table1 and the CountID measure from Table2.

cath1ynn_2-1720675229550.png

 

I can then drillthrough from this visual to Page 1 

cath1ynn_3-1720675259841.png

 

And you will see that the matrix table here has been filtered.

cath1ynn_4-1720675715326.png

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

cath1ynn_5-1720675784296.png

 

cath1ynn_6-1720675791743.png

 

Anyways, here's the sample PBIX file for you to play with.

 

 

View solution in original post

7 REPLIES 7
cath1ynn
Resolver II
Resolver II

I think this is what you want:

No Category selected:

cath1ynn_0-1720509484596.png

One Category selected:

cath1ynn_1-1720509488795.png

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.

cath1ynn_2-1720509730269.png

 

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.

@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

@TrevLc  You might need to provide a larger sample dataset for us to test with then. 

Thank you.

Here is a larger dataset

CategoryIDProduct
InternationalABFood
InternationalABCanned
InternationalACFood
InternationalADFood
InternationalAEFood
InternationalAFFood
InternationalAGFood
InternationalAHFood
InternationalAHPackaging
BikesAHStreet
BikesAHDirt
BikesBAStreet
BikesBBStreet
BikesBCDirt
BikesBCMountain
BikesBDDirt
BikesBEStreet
BikesBEDirt
BikesBFDirt
BikesBFDirt
BikesBGDirt
BikesBHDirt
BikesBIDirt
BikesBIStreet
BikesBIMountain
PackagingBIBottles
PackagingCBBottles
PackagingCCBottles
PackagingCCCarton
PackagingCCBoxes
PackagingCDCarton
PackagingCDBoxes
PackagingCEBottles
PackagingCFBottles
ComputersCFLaptop
ComputersDBLaptop
ComputersDBServer
ComputersDBCables
ComputersDCLaptop
ComputersDCCables

Dimension

Category
International
Bikes
Packaging
Computers

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.

cath1ynn_0-1720674999362.png

 

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

cath1ynn_1-1720675153277.png

 

Then on a new report page, I created a bar chart visual using the Category field from Table1 and the CountID measure from Table2.

cath1ynn_2-1720675229550.png

 

I can then drillthrough from this visual to Page 1 

cath1ynn_3-1720675259841.png

 

And you will see that the matrix table here has been filtered.

cath1ynn_4-1720675715326.png

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

cath1ynn_5-1720675784296.png

 

cath1ynn_6-1720675791743.png

 

Anyways, here's the sample PBIX file for you to play with.

 

 

Dangar332
Super User
Super User

Hi, @TrevLc 

Try below measure

ALL 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 Image

Dangar332_0-1720507695644.png

 

Regards,

Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @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:

TrevLc_0-1720567351885.png

 

and when something is selected, I need it to give this:

(Bikes is selected, and hence shows all products for IDs A and B)

TrevLc_0-1720659259145.png

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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