Creating a combined column from two separate tables in a new table with DAX

[Anonymous]

Hello

I have two tables with the following example data:

Table 1

Grid	Number of Failures
apple	1
banana	2
cherry	3
date	4

Table 2

Grid	Number of Failures
orange	3
yellow	1
blue	2
purple	3

I want to create a table through only Dax functions that shows the following

Table 3:

Number of Failures	Grid
1	apple, yellow
2	blue, banana
3	purple, orange, cherry
4	date

How would I do this so then I could create a table visual showing table 3? I would like some relationship set up such that if I also filtered with slicers on my visual they wold work as well. For example if I looked for only apple in table 1. Thank you for your help!

[saravanan_p]

Kindly attach a test data . Need more clarity.

[saravanan_p]

Thats awesome Sparta. Cheers!!

[SpartaBI]

@saravanan_p thank you 🙂
@Anonymous Please don't forget to accept the previous message as a solution for community visibility.

P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

[Syndicate_Admin]

Good morning

I wanted to know if it is possible to relate two matrices, that is, I have two tables, one of percentages and one of quantities, both tables you can see area 1 and area 2. I would need that if I click on table 1 to see the areas 2 that that table has, the same thing happens with the other table, that is, if I expand one the other expands.

It's possible?

Thank you!

[SpartaBI · Accepted solution]

@Anonymous 

Table = 
VAR _tbl1 = 
DISTINCT(
    UNION(
        DISTINCT('Table 1'[Number of Failures]),
        DISTINCT('Table 2'[Number of Failures])
    )
)
VAR _result = 
    ADDCOLUMNS(
        _tbl1,
        "Grid", 
        VAR _num = [Number of Failures]
        VAR _result1 = CONCATENATEX(FILTER('Table 1', 'Table 1'[Number of Failures] = _num ), 'Table 1'[Grid], ", ")
        VAR _result2 = CONCATENATEX(FILTER('Table 2', 'Table 2'[Number of Failures] = _num ), 'Table 2'[Grid], ", ")
        VAR _delimiter = IF( NOT ISBLANK(_result1) && NOT ISBLANK(_result2), ", ", "")         
        RETURN
            _result1 & _delimiter & _result2
    )
RETURN
    _result

Here is a link to download a sample solution file:
Creating a combined column from two separate tables in a new table with DAX 2022-08-09.pbix

Showcase Report – Contoso By SpartaBI