cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
General
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

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

 

 

 
 

Second Table.jpg

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @General ,

 

Kindly check the attachment whether help.

measure.PNG

Best Regards,

Icey

 

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

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

Hi @General ,

 

Kindly check the attachment whether help.

measure.PNG

Best Regards,

Icey

 

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

View solution in original post

amitchandak
Super User
Super User

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

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!
pranit828
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.





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

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.

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

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

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors