cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

 CPH 10 01-01-2021 CPH 6 01-02-2021 CPH 13 01-03-2021 CPH 13 01-04-2021 CPH 14 01-05-2021 CPH 2 01-06-2021 CPH 9 01-07-2021 CPH 6 01-08-2021 CPH 14 01-09-2021 CPH 18 01-10-2021 CPH 14 01-11-2021 ROS 11 01-12-2021 ROS 7 01-01-2021 ROS 2 01-02-2021 ROS 6 01-03-2021 ROS 2 01-04-2021 ROS 1 01-05-2021

1 ACCEPTED SOLUTION
Community Support

Hi @General ,

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 5
Community Support

Hi @General ,

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.

Super User

@General , better you unpivot table 2

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Super User

Hi @General

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

Frequent Visitor
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.
Super User

@General , 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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements

#### Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.