Lookupvalue error

[Dev-001]

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,

[shafiz_p · Accepted solution]

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 · Accepted solution]

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]

Hi @shafiz_p ,

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

 

 

[shafiz_p · Accepted solution]

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.

[Dev-001]

@shafiz_p ,

Thank you for your guidance. I appreciate it.

[vojtechsima]

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

[FreemanZ]

calculated column is not allowed in Direct Query.

check more here:

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about