Dataset problem with tables that can't be joined

[kalspiros]

Hi all!

So i'm having this super intriguing problem! If there were not time constrains, i'd be spending more time trying to figure it out myself!

We have two tables as seen below:

The main purpose of this exercise is:

We have a list of clients that we are rating based on 3 KPI's (a possible KPI could be "Client Size" i.e. 5 for bluechip companies, 3 for government bodies, 1 for private clients.)

Then, we have some segments where we are rating them with the same 3 KPI's. Therefore, segment "Town Planning" would probably have a 3 on the "Client Size" KPI which marks government body.

I am envisaging a slicer on clients.

Every time i will choose a client, i would like to see the absolute differences between each client's kpi with the relevant segment kpi.

The result would be something like this:

i.e. [KPI1 DIFF.] for the first cell: ABS((aaa KPI1) - (www KPI1)) = 1 - 3 = 2

I'm not expecting to have the table retruned and stored for later as might be implied by the photo above.

In essence, Client will be a slicer, Segment with be a Row slicer on a pivot table and KPI's will be the needed measure.

Therefore, the purpose would be to find which segments better align with which clients. Thus the difference between values, the lower the number, the greater the alignment.

I have already thought of some peculiar outer joins on Power Query but it's a bit daft since there's no way to join these tables and you end up appending them and using some filtered CALCULATE on an ugly , non-consice, and long table.

I am wondering whether there's some magic in it with DATATABLE, CALCULATETABLE etc but i'm kind of lost.

In a way, i'm intruiged to explore what DAX has to say for this problem rather than resorting to Power Query.

Do you think that is possible?

Many thanks in advance!

[Anonymous]

You need to perform a cross join instead of outer join in power query. If you only have a small number of KPIs, you can perform a cross join and then create a [KPI Difference] custom column for each KPI. If you have a large number of KPIs, you can unpivot the columns from the source columns, cross join the 2 tables, add ONE [KPI Difference] custom column, and finally filter on the same KPI from both columns. The first scenario is simple and straight forward. The second scenario might be a bit complicated so I have implemented it on the sample data you provided and is accessible through https://1drv.ms/u/s!AgAlCRkx-ILRgw08_O7zM62kDQEh?e=QK9MYR The excel is the source data set and the power bi reads the data from excel and then performs the transformation I described.

This is a reference in case you need help performing the cross join: https://docs.microsoft.com/en-us/power-query/cross-join

[kalspiros]

Cheers @Anonymous for your help, yep, that was sort of the solution i was thinking but not as neatly done as with a cross join. however, i'm still wondering whether there's a DAX option to achieve that? even though segments will not be that many, clients might count hundreds, thus a cross filtered table might become unmanagable and slow. i will most certainly use your solution during these early stages but i'd be interested to know whether DAX could offer a solution.

Many thanks either way 🙂