Using a conditional IF statement with a Lookup

[rax99]

I have a very complex and very extensive data structure so I have only included the tables I'm concerned with:

See the tables below (apologies for screen capture, couldn't get it to paste any other way)

So I'm looking to use dax to provide the flag value for InsideSystemLoginFlag Column inside the CallTable.

Essentially I want to know if a call was taken by the customer during their SystemLogin session (as shown in the SystemLoginTable). As you can see from the above 3 out of the 4 calls were sitting within the customer's login session.

So the logic would be something like IF CallStartTime BETWEEN SystemLogin and SystemLogout THEN 1 ELSE 0

But surprisingly this is very difficult to achieve. It adds to the complexity as It needs to go via the lookup table, and that's where I'm struggling. 

Can this be achieved by creating a new flag column with a simple DAX query?

[rax99]

Thanks @lbendlin  for the pointers, see below data in table format.

Ive also highlighted the expected values in bold.

EDIT. Yes, the result has to be immutable, a stored value in the InsideSystemLoginFlag column is what I would be expecting.

callTable

customerID	callid	CallStartTime	InsideSystemLoginFlag
1	ci_2g34	06/04/2022 00:12:45	1
2	ci_g6363	06/04/2022 16:17:45	1
3	ci_4g45	06/04/2022 21:15:45	0
4	ci_345fg	06/04/2022 10:12:45	1

SystemLoginTable

systemLogin	SystemLogout	C2ID
06/04/2022 07:10:45	06/04/2022 17:19:45	14
06/04/2022 09:17:45	06/04/2022 18:17:45	15
06/04/2022 00:10:45	06/04/2022 00:19:45	87
06/04/2022 05:12:45	06/04/2022 11:15:45	84

CustomerLookUpTable

C2ID	CustomerID
87	1
15	2
14	3
84	4

[v-shex-msft · Accepted solution]

HI @rax99,

You can add a custom column in the call table to get the result tag based on your conditions:

Tag =
VAR c2ID =
    LOOKUPVALUE ( Lookup[C2ID], Lookup[CustomerID], T1[customerID] )
VAR result =
    COUNTROWS (
        FILTER (
            SystemLogin,
            SystemLogin[C2ID] = c2ID
                && AND (
                    call[CallStartTime] >= SystemLogin[systemLogin],
                    call[CallStartTime] <= SystemLogin[SystemLogout]
                )
        )
    ) > 0
RETURN
    IF ( result, 1, 0 )

Regards,

Xiaoxin Sheng

[rax99]

Thanks, this is 80% what im looking for however I'm getting the following error: 

Im suspecting as there are multiple login entries in the SystemLogin Table which can also overlap each other (user can log on via more than one device) so a call can span into multiple logins for that day. Can we get this to work regardless of how many different logins that day as I'm only interested in if the call was in a session (any), and I don't care about how many sessions etc.?

EDIT. Please see below updated version of the SystemLoginTable:

SystemLoginTable

systemLogin	SystemLogout	C2ID
06/04/2022 07:10:45	06/04/2022 17:19:45	14
06/04/2022 09:17:45	06/04/2022 18:17:45	15
06/04/2022 00:10:45	06/04/2022 00:19:45	87
06/04/2022 05:12:45	06/04/2022 11:15:45	84
06/04/2022 09:10:40	06/04/2022 13:19:05	84

As you can see, for CustomerID =4, the callID ci_345fg spans into both the last 2 rows of the updated SystemLogin table (highlighted) so the flag in the Call table remains correct.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[lbendlin]

is the result immutable or can it be impacted by filter choices?

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523