Forum Discussion

Dev-001's avatar
Dev-001
Regular Visitor
1 year ago
Solved

Lookupvalue error

Hey mates, I have two tables; one is TableExp_M 2 and the other is Table SL 2. I also have a column in TableExp_M 2 where I used this direct query as the table was in direct query mode but its showi...
Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi Dev-001 
    In direct query mode, you would not be able to create a calculated column.

    You need to use power query merging and conditional column to handle if logic.

    If you prefer to use a measure instead of a calculated column, you can create a measure that performs the lookup:

    Desc. Service Level Measure = 
IF(
    ISBLANK(SELECTEDVALUE('TableExp_M 2'[Shipment No])),
    BLANK(),
    CALCULATE(
        MAX('Table SL 2'[s]),
        FILTER(
            'Table SL 2',
            'Table SL 2'[Row Labels] = SELECTEDVALUE('TableExp_M 2'[Service Level_1])
        )
    )
)

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Need to place Shipment No , Service Level 1 in table rows or in slicer selection. Other wise selectedvalue will return nothing and in result you will get blank values.

vojtechsima's avatar
vojtechsima
Super User
1 year ago

Dev-001 

hey PQ solution:

custom column:

let
shipment = [Shipment No], sl = [Service Level_1], 
check =if shipment = null then null else #"Table SL 2"{[Row Labels = sl]}[s]
in check


whole thing:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvYwMDBU0lHyyEzPUIrVgQgYAQV8U1MyS3PBQkCeT345XNYErjwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Shipment No" = _t, #"Service Level_1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shipment No", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Shipment No"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "sl", each let
shipment = [Shipment No], sl = [Service Level_1], 
check =if shipment = null then null else #"Table SL 2"{[Row Labels = sl]}[s]
in check)
in
    #"Added Custom"