Process and events use case in power BI

[amaaiia]

Hi,

I have a table as follows:

• process_id: identifier of a group of events
• event_id: identifier of each event (an event_id can be part of multiple process_ids)
• event_order: for each group (process_id), the order of each event (even_id) in the flow
• lables: data related to event_id

process_id	event_id	event_order	event_label1	event_label2
1	abc	0	XXX	YYY
1	def	1	UUU	AAA
1	ghi	2	LLL	KKK
2	abc	0	XXX	YYY
2	uyt	1	EEE	OOO
2	JKL	2	DDD	FFF

I want to create a page in the report where the user can see the information by levels (event_order). That is, the flow of a user would be:

1. The user first sees a table with all the event_id (and its labels) for when event_order is 0. In the example, he would see event_id=abc
2. Then, the user selects an event_id (abd because right now there is no more), and then in the table he has to see all the event_id of the next event_order (1 in this case) of groups (process_id) in which the selected event_id is (in this case, both processid=1 and 2). In the example, he would see event_id=def and uyt.
3. The user selects an event_id (uyt, for example), and then he sees all the event_ids of the next event_order (2 in this case) that the selected event_ids (both) are in (in this case, only processid=2). So, in the example, he would see only event_id=JKL
4. And so on until there are no more event_orders

I have tried to represent this using slicers for the event_order field and for event_id, but when selecting several event_id what the filter does is an OR, keeping all the process_id in which each event_id individually participates. What I would need is to apply a kind of AND with the filter, to keep the process_id in which all the selected event_id are.

Any idea how to deal with this use case?

[lbendlin]

This is similar to flattening a hierarchy. Create calculated columns that indicate parent/child relationships flattened. Level0, Level1, and Level2 in your case.

[amaaiia]

I can't have levels as columns, because this is a sample with only 3 levels, but the number of levels is dynamic

[v-achippa · Accepted solution]

Hi @amaaiia,

Thank you for reaching out to Microsoft Fabric Community.

Since the number of event_order levels in your dataset is dynamic, to achieve it use a combination of a disconnected slicer and a DAX measure that simulates AND logic across selected events:

• Create a disconnected slicer table for event selection i.e event_id, use this table in the slicer so that selection does not directly filter the main table.
• Create a AND filter DAX measure, which will return only process_ids where all selected event_ids are present.
• Use the measure to filter the table showing next event level, in the table visual where you display the next event_order, add two visual-level filters like,
  'YourTable'[event_order] = CurrentLevel + 1 and IsMatchingProcess = TRUE

This ensures that the users will only see the next step(event_order + 1), from process groups that contain all previously selected events

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @amaaiia,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @amaaiia,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @amaaiia,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa