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 showing an error.

 

Desc. Service Level =

IF(

   'TableExp_M 2'[Shipment No] = "",

   BLANK(),

   LOOKUPVALUE('Table SL 2'[s], 'Table SL 2'[Row Labels], 'TableExp_M 3'[Service Level_1])

)

 

The error is like the lookupvalue function can't be used in direct query mode. I also tried to add this column in the datamart itself but it's showing the same issue.

I would need this to be done either in direct query or in power query, please.

 

Thanks,

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.

6 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    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

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

      Hi shafiz_p ,

      Error has been resolved but it isn't showing up any visual.

       

       

      • shafiz_p's avatar
        shafiz_p
        Super User
        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"