Create Measures with multiple conditional checks

DAX DAX Calculations
1

Hello
Would like your help on how to create the measure (Total Revenue )and set data model. I have 2 sheets in excel - Products & Sales

Table 1 (Products; 3 columns) Product Index; Product Name; Unit Price

Table 2 (Sales; 8 columns) Invoice Date; Customer Name; Product Index Product Name; Product Index Alternate; Product Name Alternate; Product Quantity; Discount rate

Total Revenue = ‘Product’[Unit Price] * ‘Sales’[Quantity] * ‘Sales’[Discount Rate]

Due to data quality there are 4 product related columns (2x Name and 2x Index) so have to find product match looking at all of them

  1. check for Product Index match between Table 1 and 2, if I get it then I have all data points to calculate Sales
    b. Sometimes the Product Index is blank in Table 2 which case I should check the Product Index Alternate
    c. If Product Index Alternate is also blank then I should check the Product Name to find the Unit Price
    d. Finally if Product Name is also blank then look at Product Name Alternate

I am not sure how to create rules to match data across 4 columns.

Thank you.

2

I think the initial issue here is related to data modelling, every detail regarding Product should sit in the the Products table and then only 1 column in Sales that creates a 1 to many relationship between Product and Sales. Can you please share the pbix file?

3

Thank you.

  1. Am unable to control Sales table as you may think of it data coming from multiple point of sales , some point of sales can capture information well and information is in stored in Sales[Product Index] about which Product was sold but other times the information of product sold might be in other columns.

  2. All master product information is in Product table. Sales table is invoice level detail on specific transaction

4

Okay, but without looking at the file it is difficult to create a picture of your data. Will you be able to share it?

5

Sure I will remove any sensitive information and try to share in next few hours

6

Hello @RSBI,

Thank you for posting your query onto the Forum.

In your case, although if we calculate the “Total Sales” by using “SWITCH( TRUE() )” logic the main problem will still remain unsolved that i.e. data structure because your analysis won’t get completed just by calculating the “Total Sales” figures. Let’s say, if you want to analyze your reports based on Products then it won’t be an ideal situation if your data consists any blank rows because that will ultimately collapse your entire report just because there’s an error in designing the data.

So first and the foremost thing for you in this case would be to organize your data in an efficient manner.

Thanks & Warm Regards,
Harsh

7

Thanks for posting your question @RSBI. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

8

Hi @RSBI, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

9

Hi @RSBI, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!