Alternative to Merge Queries/Logical operations across queries

[juncco888]

Hi there...

I have been struggling a bit with something I think should be simple so I thought I would ask here.

I have two queries....    

Table1 looks like this

Table 2 like this

I was trying to create a new column in Table 2 called Result which was based on the following logic:

IF 'Table1'[Credit ($)] > 'Table2'[Total ($)], return 'Table2'[Total ($)],else return 'Table1'[Credit ($)]

This was easily accomplished by merging queries placing all of the fields in one query.   

Result = IF([Credit ($)]>[Total ($)],[Total ($)],Credit ($)])

What I am interested in knowing is how could I accomplish this without merging queries.  

Is there a way to do this in power query?

Is there a way in DAX?

I played around some with RELATED and RELATED TABLE as these queries share a relationshiop (The client field) but was unsuccesful.  

Any assitance in a non merge queries solution would be appreciated.

Thanks

Kyle

[DataInsights · Accepted solution]

@juncco888,

You were on the right track with RELATED. In the example, the tables have a 1:1 relationship:

Calculated column in Table 2:

Result = 
VAR vTable1Credit =
    RELATED ( Table1[Credit ($)] )
VAR vResult =
    IF ( vTable1Credit > Table2[Total ($)], Table2[Total ($)], vTable1Credit )
RETURN
    vResult

Result:

[juncco888]

Thanks.  That did the trick!...   I can't thank you enough as this was driving me crazy.

I noticed that this  is not working if I have a one to many relationship between the tables,.  Is there something I would do different in a one to many relationship situation?

[DataInsights]

@juncco888,

Glad to hear that works. In a one-to-many relationship, you would use RELATEDTABLE and use an aggregate function since multiple rows would be returned on the many side. Which table is on the many side?

[juncco888]

Hi there,

Table 1 is one the many side (although my sample data does not show it).  When aggregating and using RELATED TABLE will I be able to isoloate rows to be displayed.

For example, table one might have three client Kyle rows with three different credit amounts ($45, $50, $125) but I want to display only one them based on some critera.

You have been very helpful already so no problem if you dont have time to respond.  I will certainly do some additional research into this.

Thanks again!

Kyle

[DataInsights]

@juncco888,

Here's an example with a one-to-many relationship, in which you want the amount where Flag = 1.

Table1:

Table2:

Calculated column:

Result = 
VAR vTable1Credit =
    CALCULATE ( MAX ( Table1[Credit ($)] ), Table1[Flag] = 1 )
VAR vResult =
    IF ( vTable1Credit > Table2[Total ($)], Table2[Total ($)], vTable1Credit )
RETURN
    vResult

[juncco888]

Thank you!

[Ashish_Mathur]

Hi,

Share relevant/representative data in both tables and show the expected result.