Creating Table Row with data from two rows using matching criteria

[AppleMan]

I have an interesting ask that I have a few solutions in my head for how to accomplish it but before I go ahead I would like to see what others would suggest for accomplishing this.

Basically, I have a report that is comparing data from two different jobs. To do this I have the user select two jobs from a slicer and then am using MAX() and MIN() in my measures to grab either job 1 (the MIN) or job 2 (the MAX). 

One of the visuals I would like on this page is a table that has the Job and the estimated hours vs actual hours of work. But instead of having this and then needing to scroll to find the other job to compare I would like both jobs to show in one row. I need to either write measures and use those in the table or create a calculated table that does this. 

Here is an example of my current table:

This is job 1:

This is job 2 (found from just scrolling further in the table):

What I would like to show is all the data in the first image, then where the assembly and operation match for the second job show that data in the same line. 

Here is one row as an example using the data in the images above (this is for assembly 0, operation 10 for both jobs):

10593 | 0 | 10 | PLS | 20.00 | 28.80 | 0.00 | 11069 | 0 | 10 | PLS | 30.00 | 71.84 | 2.35

What would be the suggested best method to accomplish this?

[Anonymous · Accepted solution]

Hi  @AppleMan ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Slicer_Table =
DISTINCT('Table'[Job])

2. Create measure.

Flag =
var _min=MINX(ALLSELECTED('Slicer_Table'),[Job])
return
IF(
    MAX('Table'[Job])=_min,1,0)

Actual Hours_Measure =
var _min=MINX(ALLSELECTED('Slicer_Table'),[Job])
var _max=MAXX(ALLSELECTED('Slicer_Table'),[Job])
retur
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=_max),[Actual Hours])

Assembly_Measure =
var _min=MINX(ALLSELECTED('Slicer_Table'),[Job])
var _max=MAXX(ALLSELECTED('Slicer_Table'),[Job])
retur
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=1&&[Job]=_max),[Assembly])

Code_Measure =
var _min=MINX(ALLSELECTED('Slicer_Table'),[Job])
var _max=MAXX(ALLSELECTED('Slicer_Table'),[Job])
return
MAXX(
    FILTER(ALL('Table'),'Table'[group_index]=1&&[Job]=_max),[Code])

Estimated Hours_Measure =
var _min=MINX(ALLSELECTED('Slicer_Table'),[Job])
var _max=MAXX(ALLSELECTED('Slicer_Table'),[Job])
return
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=_max),[Estimated Hours])

Job_Measure =
var _min=MINX(ALLSELECTED('Slicer_Table'),[Job])
var _max=MAXX(ALLSELECTED('Slicer_Table'),[Job])
return
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=_max),[Job])

Operation_Measure =
var _min=MINX(ALLSELECTED('Slicer_Table'),[Job])
var _max=MAXX(ALLSELECTED('Slicer_Table'),[Job])
return
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=_max),[Operation])

Rework Hours_Measure =
var _min=MINX(ALLSELECTED('Slicer_Table'),[Job])
var _max=MAXX(ALLSELECTED('Slicer_Table'),[Job])
return
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=_max),[Rework Hours])

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[Anonymous]

Hi  @AppleMan ,

Here are the steps you can follow：

Way1: Use meausre to display the corresponding columns

In Power Query -- Add Column – Index Column – From 1.

Create calculated column.

group_index =
RANKX(
    FILTER(ALL('Table'),
    'Table'[Job]=EARLIER('Table'[Job])),[Index],,ASC)

Create measure.

Job_Measure =
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=11069),[Job])

Actual Hours_Measure =
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=11069),[Actual Hours])

Operation_Measure =
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=11069),[Operation])

Estimated Hours_Measure =
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=11069),[Estimated Hours])

Rework Hours_Measure =
SUMX(
    FILTER(ALL('Table'),'Table'[group_index]=MAX('Table'[group_index])&&[Job]=11069),[Rework Hours])

Set [Job] -- 10593

Result:

Way2:Add the data as a new column:

Copy Table twice - Table10593 and Table11069

For Table10593 filter only 10593 of the data, for Table11069 filter only 11069 of the data, respectively, in the two tables to add Index

Home – Merge Queries -- Merge Queries as New

Select the columns to appear:

The data will be presented in a table

Best Regards,

Liu Yang

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

[AppleMan]

Correct me if im wrong, and I probably explained this poorly, but this will not work since the code is setting the jobs manually. These columns need to change dynamically depending on what two jobs are selected from the slicer on the page. It will not always just be the two jobs I showed in the example. 

The two jobs in the pictures were just an example to explain what I need, but the user could pick two completely different jobs.