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.
Hey all, this is a copy of a previous post I made. I already accepted it as the solution, but sadly I later realised I needed to add in more logic to the DAX.
Original Post: (Firstly, a sample of my 2 tables.
Table1: Project
CustomerID | ProjectID | ProjectDateCreated
1 5 2020-13-05
2 5 2020-13-05
3 5 2021-01-01
Table2: Activities
CustomerID | ActivityType | ActivityDate
1 Call 2020-21-05
1 Visit 2020-05-05
2 Mail 2020-03-01
2 Call 2021-10-01
3 Visit 2020-08-10
What I want to do is to: Check all the activities that have taken place, after a Project was planned on that customer. So in this example activities in red should be filtered, because the ActivityDate < ProjectDateCreated on CustomerID. It doesn't matter if the Project isn't related to the Activity.
I've tried the following calculated column, to filter out the activities:
Solved! Go to Solution.
Hi @Anonymous ,
Belive that this can be done by making the following:
Relevance =
VAR _1 =
COUNTX (
FILTER (
Projects,
Projects[CustomerID] = Activities[CustomerID]
&& Activities[ActivityDate] >= Project[ProjectDateCreated] - 7
&& Activities[ActivityDate] <= Project[ProjectDateCreated] + 30
),
Projects[CustomerID]
)
RETURN
IF ( ISBLANK ( _1 ), 0, 1 )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi@Anonymous ,
It's pleasant if your problem has been solved, could you please mark the reply as Answered? that way, other community members will easily find the solution when they get the same issue.
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Belive that this can be done by making the following:
Relevance =
VAR _1 =
COUNTX (
FILTER (
Projects,
Projects[CustomerID] = Activities[CustomerID]
&& Activities[ActivityDate] >= Project[ProjectDateCreated] - 7
&& Activities[ActivityDate] <= Project[ProjectDateCreated] + 30
),
Projects[CustomerID]
)
RETURN
IF ( ISBLANK ( _1 ), 0, 1 )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |