Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 select...
Need Help
Show and Tell
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous,

     

    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!

  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous

     

    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!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-xulin-mstf
Community Support
5 years ago

Hi Anonymous,

 

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      v-xulin-mstf
      Community Support
      5 years ago

      Hi Anonymous

       

      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!