Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello. I have a small case here that I can not find a solution to by searching the forum here. Hope someone can give me a tip.
I have a model with two tables, A & B, among several.
These two are not related. I want to add a column in Table B with Campaign from Table A. It should add campaign when hits on product, customer and sales date hits in validity within campaign date range.
Is there a DAX formula that can solve this?
Solved! Go to Solution.
Hi,
This calculated column formula works
=CALCULATE(VALUES(Table1[Campaign]),FILTER(Table1,Table1[Customer]=EARLIER(Table2[Customer])&&Table1[Product ID]=EARLIER(Table2[Product ID])&&Table1[Start date]<=EARLIER(Table2[Salesdate])&&Table1[End date]>=EARLIER(Table2[Salesdate])))
Hope this helps.
HI @DJ1977,
It sounds like a general multiple date range analysis requirements, you can take a look at the following link 'start date', 'end date' part of it suitable for your sceniaro.
Regards,
Xiaoxin Sheng
@DJ1977 , Try this formula. As a new Dax column
minx(filter(TableA, TableA[StartDate] <=Tableb[SalesDate] && TableA[endDate] >=Tableb[SalesDate] && && TableA[productID] >=Tableb[productID]), TableA[]campaign])
Refer this video , how copy value from one table to another :https://www.youtube.com/watch?v=czNHt7UXIe8
Hi,
This calculated column formula works
=CALCULATE(VALUES(Table1[Campaign]),FILTER(Table1,Table1[Customer]=EARLIER(Table2[Customer])&&Table1[Product ID]=EARLIER(Table2[Product ID])&&Table1[Start date]<=EARLIER(Table2[Salesdate])&&Table1[End date]>=EARLIER(Table2[Salesdate])))
Hope this helps.
Hi @Ashish_Mathur , and thank you very much for your answer. I tryed this solution, but i got an error telling me "DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."
Both Campaign and Customer are textcolumns. Is there a way i can go around this error?
Regards
DJ
HI @DJ1977,
It sounds like a common issue when you use the 'math' operators to compare with different types of values. Please check your two table date fields to confirm if they are correctly formatted as date types.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft and thank you very much for your answer. This fixed the error and now i have a solution to my problem, with help of your tip and @Ashish_Mathur DAX formula above.
Regards
DJ
HI @DJ1977,
I'm glad to hear that my suggestion helps to solve the issue.🙂
Regards,
Xiaoxin Sheng
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |