Calculate filter based on multiple table variables

[IvensaMDH]

Hi,

I want to filter a calculation based on several table variables as below, however i am unable to reference the "Time" or "Sla" column of the "Data" table after filtering.

Is this possible and if so how would i go about doing it..? (There may be multiple filter on multiple columns).

Result = 
VAR A = FILTER(Data, Data[Type] = 1)
VAR B = FILTER(Data, Data[Type] = 2)
VAR C = FILTER(Data, Data[Type] = 3)
RETURN CALCULATE(COUNTROWS(A) / COUNTROWS(B) * COUNTROWS(C), A[Time] > B[Time], C[Sla] < A[Sla])

 Thanks in advance!

/Mathias

[Anonymous]

Hi @IvensaMDH ,

According to my understanding, it seems that you want  to create a new table with Ids that there are Time for Type 1 and Type 2 ,and then get the time duration.

If so ,please try:

New Table =
VAR _t =
    SUMMARIZE (
        'Table',
        'Table'[Id],
        "Type 1 Time", LOOKUPVALUE ( 'Table'[Time], 'Table'[Id], [Id], 'Table'[Type], 1 ),
        "Type 2 Time", LOOKUPVALUE ( 'Table'[Time], 'Table'[Id], [Id], 'Table'[Type], 2 )
    )
RETURN
    ADDCOLUMNS (
        FILTER ( _t, [Type 1 Time] <> BLANK () && [Type 2 Time] <> BLANK () ),
        "Time Diff(Minutes)",
            ( [Type 2 Time] - [Type 1 Time] ) * 24 * 60
    )

Output:

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

[IvensaMDH]

Hi @Anonymous ,

Thank you! This is exactly what i was looking for and done in an elegant way.

We do, however, have one slight edge case;

Sometimes, an entity will produce several events of the same type, but with different time. Example:

Id	Type	Time
1	1	15-03-2022 10:00:00
1	2	15-03-2022 11:00:00
2	1	15-03-2022 10:00:00
3	1	15-03-2022 10:00:00
3	2	15-03-2022 12:00:00
4	2	15-03-2022 13:00:00
1	1	15-03-2022 17:00:00
1	2	15-03-2022 19:30:00

In the above example, the entity with [Id] = 1 has produced 2 "sets" of events (a set being an event of [type] = 1 followed by an event of [type] = 2). Because of this, i have changed the query to FIRSTNONBLANKVALUE instead of LOOKUPVALUE (otherwise there may be multiple values returned).

I do however wonder, if it would be possible to traverse above table, and collect "sets" of events, producing the following:

Id	Type 1 Time	Type 2 Time
1	15-03-2022 10:00:00	15-03-2022 11:00:00
3	15-03-2022 10:00:00	15-03-2022 12:00:00
1	15-03-2022 17:00:00	15-03-2022 19:30:00

If you have any suggestions i'd love to hear them as it would be a great help for us!

Br,

/Mathias

[Anonymous]

Hi @IvensaMDH ,

Please try to use SUMMARIZE() to filter table, then you could reference the columns. For example:

Measure = 
var A= SUMMARIZE(FILTER('Data',[Type]=1),[Time],[Sla])
var B= SUMMARIZE(FILTER('Data',[Type]=2),[Time])
var C= SUMMARIZE(FILTER('Data',[Type]=3),[Sla])
RETURN CALCULATE(COUNTROWS(A) / COUNTROWS(B) * COUNTROWS(C), FILTER(A,[Time]>MAXX(B,[Time]) &&[Sla]>MAXX(C,[Sla])))

If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data to help us clarify your scenario.

How to Get Your Question Answered Quickly

How to provide sample data in the Power BI Forum

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

[IvensaMDH]

Hi @Anonymous ,

Thanks for your solution - I do, however, realize that my initial question perhaps was not entirely what i really was looking for.

Given a table like below, could you suggest a way to calculate the difference between the time of rows with type 1 against rows with type 2 of the same Id.

ie. the difference between [Time] of row 1 with [Type] = 1 and [Time] of row 2 with [Type] = 2

and for rows 4 and 5 as well since they have the same Id and Type 1 and 2

Id	Type	Time
1	1	15-03-2022 10:00:00
1	2	15-03-2022 11:00:00
2	1	15-03-2022 10:00:00
3	1	15-03-2022 10:00:00
3	2	15-03-2022 12:00:00
4	2	15-03-2022 13:00:00

To clarify, the table is logging events of different [Type] as well as the [Time] of that event and an associated [Id] of the entity producing that event. I would like to find all Id's which has an event of Type=1 AND THEN (based on Time) and event of Type=2.

The result should ideally be a table of Ids and Times:

Id	Type 1 Time	Type 2 Time
1	15-03-2022 10:00:00	15-03-2022 11:00:00
3	15-03-2022 10:00:00	15-03-2022 12:00:00

Do you have any suggestions as to how i can achieve this?

Thanks in advance!

/M

[amitchandak]

@IvensaMDH , try like

Result =
VAR A = Maxx(Data, Data[Type] = 1, Data[Time])
VAR B = Maxx(Data, Data[Type] = 2, Data[Time])
VAR C = Maxx(Data, Data[Type] = 3, Data[sal])
VAR D = Maxx(Data, Data[Type] = 1, Data[sal])
RETURN CALCULATE(COUNTROWS(A) / COUNTROWS(B) * COUNTROWS(C), A > B, C < D)