Forum Discussion

TrevLc's avatar
TrevLc
Helper III
2 years ago
Solved

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, ...
Need Help
  • cath1ynn's avatar
    cath1ynn
    2 years ago

    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.

     

     

Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

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

 

Regards,

Dangar

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

  • TrevLc's avatar
    TrevLc
    Helper III
    2 years ago

    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:

     

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

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