topic Re: LookupValue Filter search column based on row value in DAX Commands and Tips

LookupValue Filter search column based on row value

Anonymous — Tue, 10 Jan 2023 14:52:41 GMT

I have 2 tables, one is a table of projects that tell has a row telling me which way to spread the cashflow of the project (linear, backloaded, front loaded, etc.)

Table 1

Project ID Percent Complete Cashflow Type FORMULA TO RETURN %

123 .10 Backloaded .01

456 .20 Linear .20

789 .20 Frontloaded .21

The second table has the cashflow types as column labels and the rows tell me what %spend a project should be at (example below)

Table 2

Percent Complete Backloaded % Front Loaded% Linear%

.10 .01 .08 .10

.20 .03 .21 .20

I want to do a formula which I BELIEVE is a combination of LookupValue and Filter? Based on the cashflow type in the cell value from table 1 I would like to link to the correct column in table 2 and return the % complete from the correct column.

I will also add that the cell value in table 1 is not an exact match to the column name from table 2. I can update them to be exact if needed but I think the switch formula helps there?

Re: LookupValue Filter search column based on row value

AlB — Tue, 10 Jan 2023 15:56:38 GMT

Hi Anonymous

See it all at work in the attached file.

This is best done in Power Query. Place the following M code in a blank query to see the steps.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtAzBJJOicnZOfmJKakpSrE60UompmZgKSMg6ZOZl5pYBBY2t7CEC7sV5eeVwLTEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, PercentComplete = _t, CashflowType = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"PercentComplete", type number}, {"CashflowType", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"PercentComplete", "CashflowType"}, Table2, {"PercentComplete", "Type"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Value"}, {"Value"}) in #"Expanded Table2"

and your table 2 needs to be unpivoted for easier transformation

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtAzVNIBkgZQygJMGSrF6oDkjCCCxmDKCKLESCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PercentComplete = _t, #"Backloaded%" = _t, #"Frontloaded%" = _t, #"Linear%" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PercentComplete", type number}, {"Backloaded%", type number}, {"Frontloaded%", type number}, {"Linear%", type number}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PercentComplete"}, "Type", "Value"), #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Type", Order.Ascending}}), #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","%","",Replacer.ReplaceText,{"Type"}) in #"Replaced Value"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: LookupValue Filter search column based on row value

Anonymous — Tue, 10 Jan 2023 17:48:40 GMT

I ended up doing it by If's then lookupvalues pointing at the correct column. I have done something similiar to what you have above in PQ but it became really burdensome on the the functionality of the tool anytime I hit refresh. Thank you for your help