Separate Column to filter

[michellen]

Hi all

I have a large data table with 4m+ rows of events and a Category column that has the action, behaviour and outcome of the event. I need to set up a many to many filter so if you client selects action it will display the associated behaviour or outcome of the event or Reverse the filter by outcome and show associated action or behaviour and then  by behaviour to show action or outcome. An event may have single or multiple actions, outcomes or behaviours and some have just action. I thought I could unpivot but the table is too large.  There is a lookup table but its a one for one. Is there a query that would work? It feels like it should be easy but I've exhausted my searches and need help!

Event	Category	Reason
111	Action	Action 1
111	Behaviour	Behaviour 1
111	Behaviour	Behaviour 2
111	Outcome	Outcome 1
111	Outcome	Outcome 2
112	Action	Action 1
112	Action	Action 2
112	Behaviour	Behaviour 1
112	Behaviour	Behaviour 3
113	Action	Action 1
113	Behaviour	Behaviour 1
113	Outcome	Outcome 1
114	Action	Action 1
114	Behaviour	Behaviour 2
114	Behaviour	Behaviour 3
115	Action	Action 3

Thanks

Michelle

[v-xulin-mstf · Accepted solution]

Hi @michellen,

Try measure as:

Measure_Reason = 
var _tab=CALCULATETABLE(VALUES('Table'[Event]),FILTER(ALL('Table'),'Table'[Reason]=SELECTEDVALUE('Table 3'[Reason])))
Return
IF(MAX('Table'[Event]) in _tab,1,0)

 Here is the output:

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[michellen]

I'll work with that. Thanks for all your help @v-xulin-mstf !! 

[michellen]

Thats great and works so thankyou! But is it possible to go to another level to display only those items that relate to the filtered item. Eg. If we filter by Outcome 1 then only show the two events that had Outcome1 and the Reasons related to those events. Sorry struggling with this so hoping I'm explaining it properly but basically if we filter by Outcome1 (a collision type) then show the action/behaviour/outcome that led to Outcome1. In this case only Events 111 and 113 had Outcome 1. Note: There can be mulitple Outcomes, Actions and Behaviours so in example below show everything relating to events that were filtered except Outcome1. I updated the pbix with your original suggestion and then used same logic to filter by Reason. Again thanks for your help!

Separate Column to filter v2.pbix 

[v-xulin-mstf · Accepted solution]

Hi @michellen,

Try measure as:

Measure_Reason = 
var _tab=CALCULATETABLE(VALUES('Table'[Event]),FILTER(ALL('Table'),'Table'[Reason]=SELECTEDVALUE('Table 3'[Reason])))
Return
IF(MAX('Table'[Event]) in _tab,1,0)

 Here is the output:

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[michellen]

The file didn't have your latest changes but I was able to add the measure to my file and it worked. Thankyou nice work! But as this will be represented in a chart showing only the associated behaviours of each Outcome (eg. collision) this solution breaks if the EventId is not included in the visual. Is there a way to do this same measure but don't include the EventId in the visual?? Again apologies this problem does exceed my DAX ability!

[v-xulin-mstf · Accepted solution]

Hi @michellen

It's not support, the measure returned results by id.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf]

Hi @michellen,

Taking the sample data above as an example, what does your expected output look like?

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[michellen]

Thanks for your response @v-xulin-mstf 
Looking at EventId 111, there is 1Action, 2Behaviours and 2Outcomes. We want to select Action from the Category column and display the corresponding Behaviours or Outcomes but as they are all in the same column it doesn't work. Because the data is so large I can't transpose so was hoping there is Dax query that could work. 

[v-xulin-mstf]

Hi @michellen,

You can create a dimension table as:

Table 2 = VALUES('Table'[Category])

Create a measure as:

Measure = 
IF(
    ALLSELECTED('Table 2'[Category])=MAX('Table'[Category]),
    1,
    0
)

The pbix ai attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!