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

[Anonymous]

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.

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!!

[mahoneypat · Accepted solution]

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

[Anonymous]

@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!