cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nazdac911
Helper I
Helper I

In calculated column , how to replace Lookupvalue with Related and create SQL script

Hi All

I have 2 tables : Table 1 and Table 2 , (1 --> M relation)

In Table 1: I am creating the  following calculated column:

Nazdac911_0-1668243602242.png

 

 

TableA[Col Z] =

LOOKUPVALUE(

        TableB[Col X] , Table[B] [Col Y], "String_Value",

        TableA[Col PK] , TableB[Col SK] , 

        "N/A")

 

This line work great .

I need help for 2 things:

  • I want to use Related instead of lookup
  • How could I create the equivalent SQL script for this DAX script?

Thanks in advance  

2 REPLIES 2
amitchandak
Super User
Super User

@Nazdac911 , A new column in Table A

 

Coalesce(maxx(filter(Table2, Table[2] = "Value"), Table2[X]) , 0)

 

or

 

Coalesce(sumx(filter(Table2, Table[2] = "Value"), Table2[X]) , 0)

 

 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Hi 

Thank you for the fast reply .. 

I will need to digest the coalsec function , i never work with it before .. 

but unfortunality it did not work and gave me the follwoing error message 

Nazdac911_0-1668243833159.png

I updated my lookupvalue function and added the Value is a string value ( all relative columns are string columns) 

Thank you for sharng the youtube videios , they are of great value 

Regarda 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors