DAX - Calcuate a many to many mapping?

Question

I'm trying to build a calculated table, containing the mapping between different datasets. The keys I'm using to do the lookup can be repeated and I would like to generate the list of all possible combinations. In SQL, this would be a join which would generate additional rows. I'm looking to do the same in DAX, with a calculated table, however LOOKUPVALUE can only return one row and will error if it finds more than one match.

A table of multiple values was supplied where a single value was expected

I feel like it could be possible with summarise columns and a virtual relationship, however when trying this, I also get an error

=SUMMARIZECOLUMNS (
    Label[LabelText],
    User[Dim_CustomerUser_Skey],
    Computer[Dim_Computer_Skey]
    ,FILTER ( Computer, Label[Device] = Computer[Device name])
    , FILTER ( User, Label[UserName] =User[UserName])
)

but this also gives:

Calculated table 'CalculatedTable 1': A single value for column 'Device' in table 'Label' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result

How to I produce a calculated table for a many to many?

Answer 1

In SQL, there are Joins. Luckily for us DAX provide joins between tables.

But first of all, what function to use for what? Here it is:

1. Left Outer: GENERATEALL, NATURALLEFTOUTERJOIN
2. Right Outer: GENERATEALL, NATURALLEFTOUTERJOIN
3. Full Outer: CROSSJOIN, GENERATE, GENERATEALL
4. Inner: GENERATE, NATURALINNERJOIN
5. Left Anti: EXCEPT
6. Right Anti: EXCEPT

Visit : https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/