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!

v-xulin-mstf's avatar
v-xulin-mstf
Community Support
5 years ago

Hi Anonymous,

 

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

      Hi Anonymous,

       

      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!