Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
michellen
Frequent Visitor

Separate Column to filter

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!

 

EventCategoryReason
111ActionAction 1
111BehaviourBehaviour 1
111BehaviourBehaviour 2
111OutcomeOutcome 1
111OutcomeOutcome 2
112ActionAction 1
112ActionAction 2
112BehaviourBehaviour 1
112BehaviourBehaviour 3
113ActionAction 1
113BehaviourBehaviour 1
113OutcomeOutcome 1
114ActionAction 1
114BehaviourBehaviour 2
114BehaviourBehaviour 3
115ActionAction 3

 

Thanks

Michelle

2 ACCEPTED SOLUTIONS

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:

vxulinmstf_0-1625042090316.png

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!

View solution in original post

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!

View solution in original post

8 REPLIES 8
michellen
Frequent Visitor

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

michellen
Frequent Visitor

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 

michellen_0-1625031602968.png

 

 

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:

vxulinmstf_0-1625042090316.png

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!

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!

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
Community Support
Community Support

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!

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. 

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
)

vxulinmstf_0-1624952445658.png

 

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.