Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX multiple conditions

Hello boys and probaly some few girls

I have 2 tables. First table is showing city name, amount of services and date.

The other table is showing city name, variable costtypes and date columns with 12 month and different cost.

I want to calculate the cost each motnth in DAX!.

 

So for instance I want CITY CPH in January to multiply Amount from table 1 with same city and month in table 2.

So January 2021 would be 631*10 for CPH for Cost type 1 and 716*10 for CPH for Cost type 2. For City ROS it could be 716*7 for January and so forth.

 

I donw know how to do this in DAX?

 

First Table

CITYAmountDate

CPH1001-01-2021
CPH601-02-2021
CPH1301-03-2021
CPH1301-04-2021
CPH1401-05-2021
CPH201-06-2021
CPH901-07-2021
CPH601-08-2021
CPH1401-09-2021
CPH1801-10-2021
CPH1401-11-2021
ROS1101-12-2021
ROS701-01-2021
ROS201-02-2021
ROS601-03-2021
ROS201-04-2021
ROS101-05-2021

 

 

 
 

Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Kindly check the attachment whether help.

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    I would Unpivot the 2nd table and join on both(city and date) columns to get to the solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Thanks for your answer. I tried to unpivot it, but for some reason I still have date and city to be connected. And both are many to many.
      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , Try formula's like updated in last update as new columns

        Cost Type 1 Amount = [Amount]* maxx(filter(Table2, Table1[City] =Table2[City] && Table1[Date] = Table2[Date] && Table1[Variable] ="Cost type 1"),[Cost])


        Cost Type 2 Amount = [Amount]* maxx(filter(Table2, Table1[City] =Table2[City] && Table1[Date] = Table2[Date] && Table1[Variable] ="Cost type 2"),[Cost])

         

        if you want to join , you can create a concatenated column

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , better you unpivot table 2

    https://radacad.com/pivot-and-unpivot-with-power-bi
    Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

     

    Then you can get data like

    Cost Type 1 Amount = [Amount]* maxx(filter(Table2, Table1[City] =Table2[City] && Table1[Date] = Table2[Date] && Table1[Variable] ="Cost type 1"),[Cost])


    Cost Type 2 Amount = [Amount]* maxx(filter(Table2, Table1[City] =Table2[City] && Table1[Date] = Table2[Date] && Table1[Variable] ="Cost type 2"),[Cost])

     

    or you can combine that based on conditions

     

     

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Kindly check the attachment whether help.

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.