none
Dynamically filter data that hit all selected filter, or either of the selected filters RRS feed

  • Question

  • Hi,

    Im using PowerBi in Connection Live mode to query a Tabular model, and im looking for a way to dynamically filter my data based on the filter selection the user does.

    lets say I have the following table:

     R | E
     1 | A
     1 | B
     2 | A
     2 | C
     3 | A
     3 | D
     3 | B

    The user filters the data based on R column, as a multi select filter.  
    Currently the filter shows the records that match either of the selected R, when the user will select R = 1,2 he will get E=A,B,C . I want to enable the user to select the current state or filtering the data for records that matches all the selected R, which meant that when selecting R=1,2 the user will get E=A . 

    any idea how to solve that?

    Sunday, April 21, 2019 1:25 PM

Answers

  • You'll have to create a dedicated measure for this type of calculation. In the example below, [Measure] is a pre-existing measure that needs to be calculated with the all selected R condition:

    MeasureAll = 
    var Rcount = CALCULATE ( DISTINCTCOUNT(Table1[R])
    			, ALLSELECTED()
    			)
    var Eall = FILTER ( VALUES(Table1[E])
    		, CALCULATE ( DISTINCTCOUNT(Table1[R]) ) = Rcount
    		)
    return CALCULATE ( [Measure] , Eall )


    Expect me to help you solve your problems, not to solve your problems for you.

    Sunday, April 21, 2019 6:14 PM
  • This could be just one measure, but a bit complex one. You'll probably find it easier to code some helper measures, the most obvious being the one returning user's choice between 'all' and 'either', which could be e.g.

    AllOrEither = SelectedValue ( 'disconnected table'[switch], "Either" )

    Next, your main measure could go like,

    IF ( [AllOrEither = "Either"
           , DistinctCount (table[E] )
           , <the whole MeasureAll logic>
    )



    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, April 23, 2019 2:43 PM

All replies

  • You'll have to create a dedicated measure for this type of calculation. In the example below, [Measure] is a pre-existing measure that needs to be calculated with the all selected R condition:

    MeasureAll = 
    var Rcount = CALCULATE ( DISTINCTCOUNT(Table1[R])
    			, ALLSELECTED()
    			)
    var Eall = FILTER ( VALUES(Table1[E])
    		, CALCULATE ( DISTINCTCOUNT(Table1[R]) ) = Rcount
    		)
    return CALCULATE ( [Measure] , Eall )


    Expect me to help you solve your problems, not to solve your problems for you.

    Sunday, April 21, 2019 6:14 PM
  • Hi AvivZa,

    Thanks for your description.

    Per your description, I guess this would be you want.

    [calculated column]= 
    var Rcount = CALCULATE ( DISTINCTCOUNT(table[R])
    			, ALLSELECTED()
    			)
    var Eall = FILTER ( VALUES(table[E])
    		, CALCULATE ( count(table[E]),ALLEXCEPT(table,table[E]))= Rcount
    		)
    return IF(CALCULATE ( sum(table[mea]) , Eall) = BLANK(), 0,CALCULATE ( sum(table[mea]) , Eall))

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 22, 2019 6:12 AM
  • thank you for your reply Alexei. After creating the measure, how will I be able to filter the report? I mean measures can be used as filters only as visual level filters, and not report level filters.
    Tuesday, April 23, 2019 8:14 AM
  • Hi Will, thanks for your reply. 
    how can I use calculated column in a dynamic manner? since the calculated column is being calculated only when the model is processed, how can I order re-calculation whenever the user selection changes? 

    Tuesday, April 23, 2019 8:16 AM
  • Hi Will, thanks for your reply. 
    how can I use calculated column in a dynamic manner? since the calculated column is being calculated only when the model is processed, how can I order re-calculation whenever the user selection changes? 

    What is your so called calculated column in a dynamic manner like? Could you please use some sample to state your thoughts?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 23, 2019 9:16 AM
  • Hi Will, thanks for your reply. 
    how can I use calculated column in a dynamic manner? since the calculated column is being calculated only when the model is processed, how can I order re-calculation whenever the user selection changes? 

    What is your so called calculated column in a dynamic manner like? Could you please use some sample to state your thoughts?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    when using the report the user select's which Rs he wants to visualize, and usually the user will change his selection while using the report.

    for example (using the above example data):
    the user might select R = 1,2 in in an ALL mode --> will get E=A
    change the selection to R = 1,3 in ALL mode --> will get  E = A,B
    and then change the selection again to R= 1,3 in EITHER mode --> will get E=A,B,D

    all of the above will be in the same session so the model wont be reprocessed between selections 

    Thanks,

    Aviv


    Tuesday, April 23, 2019 11:35 AM
  • Why, you filter the report with the slicer on R as you initially stated. The logic in the measure will then kick it and calculate the correct result.

    Now if you also want to switch between All/Either on the fly, that'll require a disconnected selector table (could be a single column with two values, 'all' and 'either'), and an extra IF in the 'all-aware' measures, checking user's selection and applying the corresponding calculation branch.


    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, April 23, 2019 1:49 PM
  • Why, you filter the report with the slicer on R as you initially stated. The logic in the measure will then kick it and calculate the correct result.

    Now if you also want to switch between All/Either on the fly, that'll require a disconnected selector table (could be a single column with two values, 'all' and 'either'), and an extra IF in the 'all-aware' measures, checking user's selection and applying the corresponding calculation branch.


    Expect me to help you solve your problems, not to solve your problems for you.

    Not sure I follow...
    lets say I have a measure that count distinct Es, when on "all" mode, and R=1,2 I want the measure to show the number 3 (A,B,C), then lets say the user changed the selection to "either" mode and R=2,3 so I want the measure to show me 1 (A). 
    is that possible? or do I need to create a different measure for each mode?
    Tuesday, April 23, 2019 1:59 PM
  • This could be just one measure, but a bit complex one. You'll probably find it easier to code some helper measures, the most obvious being the one returning user's choice between 'all' and 'either', which could be e.g.

    AllOrEither = SelectedValue ( 'disconnected table'[switch], "Either" )

    Next, your main measure could go like,

    IF ( [AllOrEither = "Either"
           , DistinctCount (table[E] )
           , <the whole MeasureAll logic>
    )



    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, April 23, 2019 2:43 PM
  • Not sure I follow...
    lets say I have a measure that count distinct Es, when on "all" mode, and R=1,2 I want the measure to show the number 3 (A,B,C), then lets say the user changed the selection to "either" mode and R=2,3 so I want the measure to show me 1 (A). 
    is that possible? or do I need to create a different measure for each mode?

    One more point, I hear that you're using report to show the result. Based on this, with regards to the mode "all" or "either", you could convert them into input parameter. (Combox in report), then code the query based on that parameter.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 24, 2019 7:27 AM
  • You'll have to create a dedicated measure for this type of calculation. In the example below, [Measure] is a pre-existing measure that needs to be calculated with the all selected R condition:

    MeasureAll = 
    var Rcount = CALCULATE ( DISTINCTCOUNT(Table1[R])
    			, ALLSELECTED()
    			)
    var Eall = FILTER ( VALUES(Table1[E])
    		, CALCULATE ( DISTINCTCOUNT(Table1[R]) ) = Rcount
    		)
    return CALCULATE ( [Measure] , Eall )


    Expect me to help you solve your problems, not to solve your problems for you.

     Thanks Alexei, it works great!
    Can you please help me understand what ALLSELECTED() without  any parameters does?
    Monday, April 29, 2019 12:37 PM
  • Extremely roughly speaking, it makes the Calculate honor slicers but ignore what's on rows/columns in the current visual, or in other words get the result corresponding to current visual's grand total cell.

    For the scientific explanation refer to https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/


    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, April 30, 2019 7:31 AM
  • You'll have to create a dedicated measure for this type of calculation. In the example below, [Measure] is a pre-existing measure that needs to be calculated with the all selected R condition:

    MeasureAll = 
    var Rcount = CALCULATE ( DISTINCTCOUNT(Table1[R])
    			, ALLSELECTED()
    			)
    var Eall = FILTER ( VALUES(Table1[E])
    		, CALCULATE ( DISTINCTCOUNT(Table1[R]) ) = Rcount
    		)
    return CALCULATE ( [Measure] , Eall )


    Expect me to help you solve your problems, not to solve your problems for you.

    I actually added another VAR in Measure All :


    MeasureAll = var Rcount = CALCULATE ( DISTINCTCOUNT(Table1[R]) , ALLSELECTED() ) var Eall = FILTER ( VALUES(Table1[E]) , CALCULATE ( DISTINCTCOUNT(Table1[R]) ) = Rcount )

    var TableAll = FILTER(Table1,Table1[E] in Eall
    return CALCULATE ( [Measure] , TableAll )

    which worked great as long I wasnt interested in other columns from Table 1:

    lets say there are actually 3 columns in Table 1 
     R | E | F
     1 | A | 8
     1 | B | 9
     2 | A | 8
     2 | C | 8
     3 | A | 7
     3 | D | 9
     3 | B | 9

    I try to operate MeasureAll as I wrote above but as a second level calculation,  for each F :
    for example when R=1,3 I expect to get :
    F | MeasureAll Result 
    7 | 1
    8 | 1
    9 | 2 

    but I dont get any values in the measure when I try to get the above

    any idea why?

    Tuesday, April 30, 2019 11:37 AM
  • While TableAll is a different filter from Eall, I have no idea why you'd add this step.

    To understand the results, just follow the logic of the measure being calculated for each cell in your result table.

    Rcount = 2
    (for F = 7) values(table1[e]) is just "A", calculate returns 1, which doesn't equal Rcount, so we get an empty set for Eall
    (for F = 8) values(table1[e]) is just "A" again (remember R is 1 or 3), calculate returns 1...
    (for F = 9) values(table1[e]) is B&D, calculate returns 2 only for B, and you should actually see your [Measure] for R={1,3}, E=B, and F=9 - no idea why it comes out blank.


    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, April 30, 2019 8:20 PM
  • I added the step in order to enable using other columns in Table1, for example - column F in the example I mentioned above. As fat as I understood in Eall I have only E values, I have no F values while TableAll has all the columns in Table1 just filtered to have E that appear in Eall

    Wednesday, May 1, 2019 10:53 AM
  • You're turning a cross-filter into a direct filter, and I can't come up with an example off the top of my head when that'll make a difference. Obviously it doesn't with the logic you're trying to implement. Actually, I wasn't able to deduce the logic that would produce your example values when sliced by F (although I admit I didn't try too hard :-).

    Expect me to help you solve your problems, not to solve your problems for you.

    Friday, May 3, 2019 4:14 PM