Calculated Column in Sales Fact based on Multiple Criteria


#1

Hello Sam:
Good evening and impressive amount of advise and training materials that you have produced.
Sam, hoping you can help me with my issue.

Attachment:
** Modified excel file from Enterprise DNA Demo.
Adi_EnterpriseDNAFreeTrainingDemoData.xlsx (80.1 KB)

The attached Excel file provides details of the issue outlined below.

Business Case:
In many companies the Promotion information and Sales History information are not combined resulting in Promotion ID code not carried in the Sales Data table. This results in Promotion performance analysis being done via complex programming of hard coded reports.
Objective:
Include the Promotion ID in the Sales Data table making it easier to analyze in Power BI.
Challenge:
Need to include a Promotion ID into the Sales Data table based on Multiple criteria: Dates, Product and Location. These criteria are provided in new Promotion related tables: (a) Promotion ID, Dates and Discount (b) Products on Promotion © Location on Promotion.
Ignorance:
(a) Do not know how to include multiple criteria in new Calculated column using DAX.
(b) Also unclear how to use the Date Range within a Calculated column.

Thanking you in advance for all your help.
Best regards…Adi K. Asavaid


#2

Yes you are absolutely correct that this can sometimes seem quite complex.

You are in luck though as I covered the techniques extensively in this particular member only event recently.

During this event I covered all the modelling and DAX techniques you need to use to run this type of analysis.

You can download the resources and see how I completed it also.

The key thing here is you don’t need to complete this how you are thinking about it right now. There is a much better way that I run through in detail in the workshop.

See how you go learning about it. I think you’ll be pleasantly surprised at how efficiently you can do this.


#3

Hello Sam:
Many thanks for your response and the link to the appropriate material.
I will review the material you have pointed to and get back to you if I have more questions.
Thanks and best regards…Adi
Adi K. Asavaid