Forum Discussion

ekor-sembilan's avatar
ekor-sembilan
Frequent Visitor
3 years ago
Solved

Lookupvalue

Hello,

 

Hi, I am fairly new to Power BI  and have a problem I cannot solve, I'm facing a problem when trying to find the vehicle data using the following table information.
I need a measure that I can get the vehicle data into the target table.

 

Thank you for your help.

 

Here is the information :

 

Tabel 1 
VehicleIDUserId
CAR73e73f05
CAR634abdc1
BIKE5d2f1ebb
BIKE465b85d0
BIKE3bd84da7
MOTORBIKEd3ed03ad

 

 

Table 2 
VehicleIDVechicle
BIKEBike
CARCar
ECARElectric car
MOTORBIKEMotorbike

 

Tabel 3 
UserIdVehicle
d3ed03ad 
5d2f1ebb 
465b85d0 
73e73f05 
3bd84da7 
634abdc1 

 

Here is my target :

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ekor-sembilan ,

    I created a sample pbix file(see the attachment), please check if that is what you want. You can create a calculated column as below in 'Table 1' to get it:

    Vehicle = 
CALCULATE (
    MAX ( 'Table 2'[Vechicle] ),
    FILTER ( 'Table 2', 'Table 2'[VehicleID] = 'Table 1'[VehicleID] )
)

    Or you can create a measure as below to get it:

    Measure =
VAR _selvid =
    SELECTEDVALUE ( 'Table 1'[VehicleID] )
RETURN
    CALCULATE (
        MAX ( 'Table 2'[Vechicle] ),
        FILTER ( 'Table 2', 'Table 2'[VehicleID] = _selvid )
    )

    Best Regards

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    ekor-sembilan OK, so you can't use a measure if you want it as a calculated column in a table. Here is the calculated column form:

    Column in Table 3= 
  VAR __UserID = [UserId]
  VAR __VehicalID = MAXX(FILTER('Table 1', [UserID] = __UserID), [Vehicle])
  VAR __Vehical = MAXX(FILTER('Table 2', [VehicalID] = __VehicalID), [Vehical])
RETURN
  __Vehical

    This assumes no relationships. If you have relationships you can probably use RELATED.

    • ekor-sembilan's avatar
      ekor-sembilan
      Frequent Visitor
      3 years ago

      Hello Greg,

      Thanks for your quick reply. 
      But, it's strange that the value is not the same. Here is the capture 

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ekor-sembilan ,

    I created a sample pbix file(see the attachment), please check if that is what you want. You can create a calculated column as below in 'Table 1' to get it:

    Vehicle = 
CALCULATE (
    MAX ( 'Table 2'[Vechicle] ),
    FILTER ( 'Table 2', 'Table 2'[VehicleID] = 'Table 1'[VehicleID] )
)

    Or you can create a measure as below to get it:

    Measure =
VAR _selvid =
    SELECTEDVALUE ( 'Table 1'[VehicleID] )
RETURN
    CALCULATE (
        MAX ( 'Table 2'[Vechicle] ),
        FILTER ( 'Table 2', 'Table 2'[VehicleID] = _selvid )
    )

    Best Regards