Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Add column based on multiple columns conditions

    Posted 28 days ago
    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
    ------------------------------


  • 2.  RE: Add column based on multiple columns conditions

    Top Contributor
    Posted 24 days ago
    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
    ------------------------------



  • 3.  RE: Add column based on multiple columns conditions

    Posted 23 days ago
    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
    ------------------------------