cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wayers
Helper IV
Helper IV

DAX Help - Can a Measure be created to match a table merge in Power Query

Hello Community,

I'm in search of whether I can create what I have achieved with Power Query using a DAX Measure. I have merged two tables so that I can get the rows of data to match for further calculations.

 

PBIX file for example

 

In this example have 3 tables, Test Data, Factor Info, and Merge Table. The Merge Table is from combining Test Data and Factor Info using Factor ID as the first match and POSI as the second match.

let
    Source = Table.NestedJoin(#"TEST DATA", {"FACTOR ID", "POSI"}, #"FACTOR INFO", {"FACTOR ID", "POSI"}, "FACTOR INFO", JoinKind.LeftOuter),
    #"Expanded FACTOR INFO" = Table.ExpandTableColumn(Source, "FACTOR INFO", {"FACTOR ID", "POSI", "FACTOR VALUE"}, {"FACTOR INFO.FACTOR ID", "FACTOR INFO.POSI", "FACTOR INFO.FACTOR VALUE"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FACTOR INFO", "Custom", each [DATA]*[FACTOR INFO.FACTOR VALUE]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom", "FACTORED VALUE"}})
in
    #"Renamed Columns"

 Tables used and created shown below.

TABLES.PNG

 

I would like to have a measure that accomplishes the same column that I was able to create in the Merge Table called "Factored Value" without the need to merge the tables.

 

The process for the measure would be something like...

First match, Test Data[Factor ID] = Factor Info[Factor ID]

Second with the above matched, match, Test Data[POSI] = Factor Info[POSI]

With these matched, resolve, Test Data[Data] * Factor Info[Factor Value]

 

A measure would result in what is delivered in the Merge Table[Factored Value].

 

Can this be a measure if so any offers on a solutions? Or is it best done in Power Query?

 

Any help is appreciated!!

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

I looked at your model.  You can do it two ways.

 

1. You can make a new column in both tables that is the concatenation of FactorID and POSI.  You can then make a relationship between the two tables and use the RELATED function to call it from one table to the other in a column or measure

 

2. You can keep no relationship between the tables like now and use a formula like this in a new calculated column on the Test Data column.

 

FACTORED VALUE Column =
var thisID = 'TEST DATA'[FACTOR ID]
var thisPOSI = 'TEST DATA'[POSI]
var factorvalue = CALCULATE(MIN('FACTOR INFO'[FACTOR VALUE]), 'FACTOR INFO'[FACTOR ID] = thisID, 'FACTOR INFO'[POSI]=thisPOSI)
return 'TEST DATA'[DATA] * factorvalue
 
I wasn't sure what aggregation you would do in a measure, but you could use a variation on that expression to convert it to a measure expression.
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User
Super User

I looked at your model.  You can do it two ways.

 

1. You can make a new column in both tables that is the concatenation of FactorID and POSI.  You can then make a relationship between the two tables and use the RELATED function to call it from one table to the other in a column or measure

 

2. You can keep no relationship between the tables like now and use a formula like this in a new calculated column on the Test Data column.

 

FACTORED VALUE Column =
var thisID = 'TEST DATA'[FACTOR ID]
var thisPOSI = 'TEST DATA'[POSI]
var factorvalue = CALCULATE(MIN('FACTOR INFO'[FACTOR VALUE]), 'FACTOR INFO'[FACTOR ID] = thisID, 'FACTOR INFO'[POSI]=thisPOSI)
return 'TEST DATA'[DATA] * factorvalue
 
I wasn't sure what aggregation you would do in a measure, but you could use a variation on that expression to convert it to a measure expression.
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

@mahoneypat 

Thanks for the help!

I chose to use the calculated column approach you provided and it is working great! Not needing to additional relationships to the tables (using the calculated column approach) helps to keep my data schema clean for other down stream calculations.

 

Thanks!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.