I am trying to insert a new column that says if column C = CUSTOMER 1, input Column A date, else input Column B date. How can i create this column to pull the correct date from A or B based on a customer Name.
I need to pull report history for 1 customer that includes all need dates by their request date, but all other customer need dates by our commit date.
Hello @wweber,
Thank You for posting your query onto the Forum.
There’re couple of methods to achieve the results. Method 1 is more of a manual whereas Method 2 is much more dynamic. Let’s see the results of both the methods one-by-one -
Method 1:- Manual Approach
In this method, we need to type the name of each customer inside the condition for whom we want the dates from “Column A”. So for example, since we want the date from Column A for Customer 1 who is in a Column C. Below is the generalized DAX formula provided for the reference -
Dates Based On Method 1 =
IF( Table_Name[Column_C] IN { "Customer 1" } ,
Table_Name[Column A] ,
Table_Name[Column B] )
In this method, what actually happens is, we need to type in the name of each and every customer, in case, if we want the dates from Column A for more than one Customer. So for example, if we want the dates from Column A for Customer 1 and Customer 5 then the name of Customer 5 also needs to be added manually inside those curly brackets. So now, DAX becomes like this -
Dates Based On Method 1 =
IF( Table_Name[Column_C] IN { "Customer 1" , "Customer 5" } ,
Table_Name[Column A] ,
Table_Name[Column B] )
This method becomes cumbersome when there’re more number of customers in order to retrieve this type of results. And that’s where Method 2 comes in.
Method 2:- Dynamic Approach
In this method, we’ll create/have a list of customer name’s for whom we want the dates from Column A and for other customer’s, dates from Column B will be implemented. And this “Customer Names List” table will be the dimension table which in turn will be connected with the original table (or fact table).
Once that’s done then we’ll write the below provided DAX formula but this time around, it’ll be more dynamic than the previous approach where we were required to write each customer name manually inside the formula -
Dates Based On Method 2 =
IF( Fact_Table_Name[Column_C] = RELATED( Dimension_Table_Name[Colum_A] ) ,
Fact_Table_Name[Column_A] ,
Fact_Table_Name[Column_B] )
I’m also attaching the working of the PBIX file based on both the approaches mentioned above for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Thanks and Warm Regards,
Harsh
Retrieval Of Customer Dates - Harsh.pbix (21.0 KB)
For this purpose the Manual creation of the Customer 1 is perfect for my scenario since this effort is needed for a single one customer driven report. Thank you so much !