Power BI Exchange

Hello guys
Please help me
In table SERVICES i want to add a column that will match the price from the table PRICE for each company , for the same year and only for services 1 and 2
ED
On first row price =  100 because company is AB, year is 2021, Service type 1
On row 10 price = 150 because company is AB, year is 2022, Service type 1
I tried with column from examples but cannot use because there are to many positions, i also tried IF(AND... but cannot set the rule for all companies
Thank you



------------------------------
Mihai Stanca
Acasa
------------------------------

Create a concatenation (compound key) basically take Company and Service and combine into one new column. Do the same for the Price table, you can then merge the queries in power query and pull the matching price across into the Services table, or create a relationship in power bi editor and just reference the services table in any formulas

------------------------------
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
------------------------------

Original Message:
Sent: Jun 03, 2022 02:16 AM
From: Mihai Stanca
Subject: Add column based on multiple columns conditions

Hello guys
Please help me
In table SERVICES i want to add a column that will match the price from the table PRICE for each company , for the same year and only for services 1 and 2
ED
On first row price =  100 because company is AB, year is 2021, Service type 1
On row 10 price = 150 because company is AB, year is 2022, Service type 1
I tried with column from examples but cannot use because there are to many positions, i also tried IF(AND... but cannot set the rule for all companies
Thank you



------------------------------
Mihai Stanca
Acasa
------------------------------

Hi Mihai,

I have used the compound key method, and it will work. Another option would be to use the DAX function LOOKUPVALUE().
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

My Price = LOOKUPVALUE( 'PRICE'[Price], 'PRICE'[Company], [Company], 'PRICE'[Date],[Date], 'PRICE'[Service type], [Service type], 0 )

The zero is for an alternate value if none are found in the search. The value that is returned when there is no value or more than one value in the specified column; if omitted, BLANK is returned for no value and an error is returned for more than one value.

Hope that helps,

------------------------------
Mark Wiley
Senior Solutions Architect
GraVoc
OH
------------------------------

Original Message:
Sent: Jun 03, 2022 02:16 AM
From: Mihai Stanca
Subject: Add column based on multiple columns conditions

Hello guys
Please help me
In table SERVICES i want to add a column that will match the price from the table PRICE for each company , for the same year and only for services 1 and 2
ED
On first row price =  100 because company is AB, year is 2021, Service type 1
On row 10 price = 150 because company is AB, year is 2022, Service type 1
I tried with column from examples but cannot use because there are to many positions, i also tried IF(AND... but cannot set the rule for all companies
Thank you



------------------------------
Mihai Stanca
Acasa
------------------------------