Qfyilyi

I am trying to compare to sets of data that are very similar. I have done a bridge relation and used M:M relationship on PowerBI but I am still not getting the result I want.

Here is an example of the data:
Dataset 1

Name | Service | Usage 

 A   |    1    |  10

 A   |    2    |  20

 B   |    1    |  10

 B   |    2    |  10

 C   |    1    |  20

 C   |    2    |  10

Dataset 2

Name | Service | Usage 

 A   |    1    |  40

 A   |    2    |  20

 B   |    1    |  40

 B   |    2    |  10

 C   |    1    |  40

 C   |    2    |  10

Desired output

Name | Service | Usage 1 | Usage 2  

 A   |    1    |   10    |  40

 A   |    2    |   20    |  20

 B   |    1    |   10    |  40

 B   |    2    |   10    |  10

 C   |    1    |   20    |  40 

 C   |    2    |   10    |  10

Is this possible in PowerBI?

One approach (as suggested in comments), is to separate the distinct Name and Service values into separate dimension tables, in the query editor:

Names:

= Table.FromList(List.Distinct(List.Combine({#"Dataset 1"[Name], #"Dataset 2"[Name]})),Splitter.SplitByNothing(),{"Name"})

Services:

= Table.FromList(List.Distinct(List.Combine({#"Dataset 1"[Service], #"Dataset 2"[Service]})),Splitter.SplitByNothing(),{"Service"})

Create the DAX measures you want:

Usage 1 = SUM ( 'Dataset 1'[Usage] )

Usage 2 = SUM ( 'Dataset 2'[Usage] )

Now create relationships between the fact tables (Dataset 1, Dataset 2) and the dimension tables (Names, Services):

Then simply layout the visual as required:

Another approach may be to combine your dataset fact tables into one table, with an added "dataset" column:

Create your "combined" table in the query editor.

Combined Table:

= Table.Combine({Table.AddColumn(#"Dataset 1", "Dataset", each "Dataset 1", type text), Table.AddColumn(#"Dataset 2", "Dataset", each "Dataset 2", type text)})

Now use this table as your single source - either with a crosstab visual:

Or by adding separate measure for each dataset:

Usage 1 = CALCULATE ( SUM('Combined Data'[Usage]), 'Combined Data'[Dataset] = "Dataset 1" )

Usage 2 = CALCULATE ( SUM('Combined Data'[Usage]), 'Combined Data'[Dataset] = "Dataset 2" )