Dax Measure to check if event happend between two datetimes.

[rafal_walisko]

Hello, I have one table that includes some events that happend between two datetimes (start/endtime) with corresponding device.

I have also table with logs where I've got devices and timestamp related to some actions.

Table A

ID	Device	Date	Start Time	End Time
1	A	01.01.2022	01.01.2022 15:00	01.01.2022 17:00
2	B	02.01.2022	02.01.2022 13:10	02.01.2022 13:15
3	C	04.01.2022	04.01.2022 13:10	04.01.2022 13:11

Table B

Device	Time	Action
A	01.01.2022 15:12	x
B	02.01.2022 13:11	y
C	04.01.2022 17:10	z
A	01.01.2022 15:15	z
A	01.01.2022 15:15	x

In a result I'd like to obtain table like this, where I'will have  summary of counts of each action that was taken on device in specific date.

Result table

ID	Day	Device	Action X	Action y	Action Z
1	01.01.2022	A	2	0	1
2	02.01.2022	B	0	1	0
3	04.01.2022	C	0	0	0

How I can obtain such measures? I know that I will need to have measure for each action, but the number of actions will be low (around 5-6).

[rafal_walisko]

Thank you @v-yinliw-msft  @CNENFRNL 

in this example the cardinality is one to many because of only 3 examples.

How can I work with it when the first table would look like this

ID	Device	Date	Start Time	End Time
1	A	01.01.2022	01.01.2022 15:00	01.01.2022 17:00
2	B	02.01.2022	02.01.2022 13:10	02.01.2022 13:15
3	C	04.01.2022	04.01.2022 13:10	04.01.2022 13:11

4

C

04.01.2022

04.01.2022 16:10

04.01.2022 16:11

5

A

06.01.2022

06.01.2022 16:10

06.01.2022 16:11

[CNENFRNL]

[v-yinliw-msft]

Hi @rafal_walisko ,

You can try this method:

New a table:

ResultTable =
SUMMARIZE (
    'TableA',
    'TableA'[ID],
    "Day", MAX ( 'TableA'[Date].[Date] ),
    "Device", MAX ( 'TableA'[Device] ),
    "Action X",
        CALCULATE (
            COUNTROWS ( TableB ),
            FILTER (
                'TableB',
                'TableB'[Time] >= MIN ( 'TableA'[Start Time] )
                    && 'TableB'[Time] <= MAX ( 'TableA'[End Time] )
                    && 'TableB'[Action] = "x"
            )
        ),
    "Action Y",
        CALCULATE (
            COUNTROWS ( TableB ),
            FILTER (
                'TableB',
                'TableB'[Time] >= MIN ( 'TableA'[Start Time] )
                    && 'TableB'[Time] <= MAX ( 'TableA'[End Time] )
                    && 'TableB'[Action] = "y"
            )
        ),
    "Action Z",
        CALCULATE (
            COUNTROWS ( TableB ),
            FILTER (
                'TableB',
                'TableB'[Time] >= MIN ( 'TableA'[Start Time] )
                    && 'TableB'[Time] <= MAX ( 'TableA'[End Time] )
                    && 'TableB'[Action] = "z"
            )
        )
)

The result is :

Here is my PBIX file.

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL · Accepted solution]