DAX Comparing Non - Related Dates

Sample PBI file for forum.pbix (58.2 KB)

Hi,

I have attached a simplified sample PBIx File with some mock-up data that I’m working on

I’m trying to create some DAX in my appointments table that looks up against the correspondence table and checks whether there is a correspondence type of “Pre Call” and then whether the date for the correspondence type is less than 2 working days before the appointment type. This would only be for those Meetings with a type of “Initial Call”

The only link between the correspondence and the appointments is the participant table.

In terms of a result id be looking for something like the below. A simple Binary 1,0 column. If it was possible to pull back the meeting for which the Dax has found the match that would be a bonus.

image

Is this possible?

Thanks

1 Like

Hi @sherkhan, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • Search the Enterprise DNA On-demand Platform on topics related to your post

Thanks

Hello @sherkhan ,
I have created the following measures to achieve a solution to the problem you have raised.
If you can share the data files, I could incorporate the date table and contemplate that it is 2 business days.

# of Correspondence = COUNTROWS( Correspondences )

Pre Call in 2 Working days = 
VAR _ParticipantID =
SELECTEDVALUE( Appointments[Participant ID] ) 
VAR _MeetingType = "Initial Call" 
VAR _CorrespondenceType = "Pre Call" 
VAR _MeetingDate = SELECTEDVALUE( Appointments[Date/Time] ) 
VAR _Count = CALCULATE(
    [# of Correspondence],
    FILTER(
        Participants,
        Participants[Participant ID] = _ParticipantID
    ),
    FILTER(
        Appointments,
        Appointments[Meeting Type Description] = _MeetingType
    ),
    TOPN(
        1,
        FILTER(
            Correspondences,
            Correspondences[Correspondence Type] = _CorrespondenceType &&
            DATEDIFF(
                Correspondences[Date/Time],
                _MeetingDate,
                DAY
            ) < 2
        ),
        Correspondences[Date/Time],
        DESC
    )
) 
VAR _Result = IF( _Count > 0, 1, 0 ) 

RETURN
_Result

Pre Call in 2 Working days Total = 
VAR _VirtualTable =
ADDCOLUMNS(
    Appointments,
    "@PreCall2Days", [Pre Call IN 2 Working days]
) 
VAR _Result = IF(
    HASONEVALUE( Appointments[Meeting ID] ),
    [Pre Call IN 2 Working days],
    SUMX( _VirtualTable, [@PreCall2Days] )
) 

RETURN
_Result

Regards,

2 Likes

Thanks @jafernandezpuga ,
I will give this a try and get back to you.

Thanks for getting back to me

1 Like

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

Hi @sherkhan, we’ve noticed that no response has been received from you since May 10th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @sherkhan, due to inactivity, 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.