fetch value from different table based on condition using M-Code

[Sah_123]

I have return a dax code below but wanted to achieve this same logic with m-code.
here is the dax code.

Code = IF(IF('Prod'[Material]="Others", LOOKUPVALUE('Master'[Code], 'Master'[Material], 'Prod'[Material]), CALCULATE(FIRSTNONBLANK('Plan'[Code],1), FILTER('Plan', 'Plan'[Material]='Prod'[Material] && 'Prod'[area]='Plan'[area] && 'Prod'[Date]='Plant'[Date])))=BLANK()

, "DOM VC"

, IF('Prod'[Material]="Others", LOOKUPVALUE('Master'[Code], 'Master'[Material], 'Prod'[Material]), CALCULATE(FIRSTNONBLANK('Plan'[Code],1), FILTER('Plan', 'Plan'[Material]='Prod'[Material] && 'Prod'[area]='Plan'[area] && 'Prod'[Date]='Plant'[Date]))))

[Omid_Motamedise · Accepted solution]

use this

let
Source = ... , // your initial data source for 'Prod' table
#"Merged Queries" = Table.NestedJoin(Source, {"Material"}, Master, {"Material"}, "MasterTable", JoinKind.LeftOuter),
#"Expanded Master" = Table.ExpandTableColumn(#"Merged Queries", "MasterTable", {"Code"}, {"MasterCode"}),

AddCode = Table.AddColumn(#"Expanded Master", "Code", each if [Material] = "Others"
then [MasterCode]
else
let
PlanFiltered = Table.SelectRows(Plan, each [Material] = [Material] and [area] = [area] and [Date] = [Date]),
FirstNonBlank = if Table.RowCount(PlanFiltered) > 0 then PlanFiltered{0}[Code] else null
in
FirstNonBlank
),

ReplaceBlank = Table.TransformColumns(AddCode, {{"Code", each if _ = null then "DOM VC" else _, type text}})
in
ReplaceBlank

[Anonymous · Accepted solution]

Hi @Sah_123 ,

Please add a custom column like:

let 
lookupMaster = if [Material] = "Others" then try Table.SelectRows(Master, (x)=> x[Material] = [Material])[Code]{0} otherwise "DOM VC" else null,
lookupPlan = if [Material] <> "Others" then try Table.SelectRows(Plan, (x)=> x[Material] = [Material] and x[area] = [area] and x[Date] = [Date])[Code]{0} otherwise "DOM VC" else null,
finalResult = if [Material] = "Others" then lookupMaster else lookupPlan
in 
finalResult

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Omid_Motamedise · Accepted solution]

use this

let
Source = ... , // your initial data source for 'Prod' table
#"Merged Queries" = Table.NestedJoin(Source, {"Material"}, Master, {"Material"}, "MasterTable", JoinKind.LeftOuter),
#"Expanded Master" = Table.ExpandTableColumn(#"Merged Queries", "MasterTable", {"Code"}, {"MasterCode"}),

AddCode = Table.AddColumn(#"Expanded Master", "Code", each if [Material] = "Others"
then [MasterCode]
else
let
PlanFiltered = Table.SelectRows(Plan, each [Material] = [Material] and [area] = [area] and [Date] = [Date]),
FirstNonBlank = if Table.RowCount(PlanFiltered) > 0 then PlanFiltered{0}[Code] else null
in
FirstNonBlank
),

ReplaceBlank = Table.TransformColumns(AddCode, {{"Code", each if _ = null then "DOM VC" else _, type text}})
in
ReplaceBlank

[lbendlin]

Utilize DAXFormatter.com to better visualize your code

Code =
IF (
    IF (
        'Prod'[Material] = "Others",
        LOOKUPVALUE ( 'Master'[Code], 'Master'[Material], 'Prod'[Material] ),
        CALCULATE (
            FIRSTNONBLANK ( 'Plan'[Code], 1 ),
            FILTER (
                'Plan',
                'Plan'[Material] = 'Prod'[Material]
                    && 'Prod'[area] = 'Plan'[area]
                    && 'Prod'[Date] = 'Plant'[Date]
            )
        )
    )
        = BLANK (),
    "DOM VC",
    IF (
        'Prod'[Material] = "Others",
        LOOKUPVALUE ( 'Master'[Code], 'Master'[Material], 'Prod'[Material] ),
        CALCULATE (
            FIRSTNONBLANK ( 'Plan'[Code], 1 ),
            FILTER (
                'Plan',
                'Plan'[Material] = 'Prod'[Material]
                    && 'Prod'[area] = 'Plan'[area]
                    && 'Prod'[Date] = 'Plant'[Date]
            )
        )
    )
)

Then consider refactoring the code - seems to be some redundancy.

Lastly,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...