# Dynamically filter data that hit all selected filter, or either of the selected filters • ### 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

• 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 )```

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>
)```

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 )```

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

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
• 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
• 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
• 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.

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.

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>
)```

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 )```

Thanks Alexei, it works great!
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/

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 )```

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.

• • 