Conditional data filtering

[DataGeek16]

Hi PBI Experts!

Need help with the following:

I am trying to filter a particular widget's data based on another widget. 

In this case, widget A has distinct user counts and widget B has counts of purchases (not distinct) from different stores online. 

When widget A falls below 5, I should hide data from widget B. 

I made it work using DAX if functions. However, there is a problem.

For the scenario where widget A is above 5, widget B still doesn't show data when for a particular online store the distinct count of users falls below 5. I need to handle this scenario. Please advise.

Also, to make it simple, is there a way to conditionally hide data based on another widget. In my usecase, if A falls below 5, don't show anything on B and if A is >=5, then show everything on B regardless of the individual distinct counts. 

[Ashish_Mathur]

Hi,

Maybe something like this

=IF([measure1]<5,BLANK(),[measure2])

Measure1 is the distinctcount measure.  Measure2 is the count measure.

[DataGeek16]

Thanks for replying.

Tried that. This doesn't work as both measure 1 and measure 2 come from the same dataset and although distinct count (which is measure 1) yields > 5;

Measure 2 (counts) checks for individual store distinct counts within its data and displays BLANK() if the distinct count of users for a particular store is < 5. 

I am using a stacked bar chart to display the counts along with store names. Please suggest if this could work well with a different type of widget. 

[Ashish_Mathur]

Hi,

Share a simple dataset and show your expected result there.  Show the result in a simple Table.  Once the Table shows the correct result, we can always make a chart from that Table.

[DataGeek16]

UserID	Stores	Language City
1	A	English	 Redmond
2	B	French	 Hyderabad
3	C	German	 Redmond
4	D	English	 Hyderabad
5	A	French	 Redmond
6	B	German	 Hyderabad
7	C	English	 Redmond
8	D	French	 Hyderabad
9	A	German	 Redmond
10	B	English	 Hyderabad
1	C	French	 Redmond
2	D	German	 Hyderabad
3	A	English	 Redmond
4	B	French	 Hyderabad
5	C	German	 Redmond
6	D	English	 Hyderabad
1	A	German	 Redmond
1	A	German	 Redmond
1	A	German	 Redmond

Attached some sample data and snapshot of how the data reacts to my filters. For the Stacked bar chart in the visual, the calculation is as follows:

MSR_UsersandStores = IF(DISTINCTCOUNT('Sample'[UserID]) >=4, count('Sample'[Stores]),0) 

Data without report filters - correctWhen I filter on German, although distinct count > 5, it doesn't display anything on stacked bar chart

Thanks again for your help. 

[v-juanli-msft]

Hi @DataGeek16

Try this formula

MSR_UsersandStores = IF(CALCULATE(DISTINCTCOUNT([UserID]),ALL(Sheet3))>=4,COUNTROWS(Sheet3),0)

Best Regards

Maggie

[Anonymous]

Based on your current logic,  IF(DISTINCTCOUNT('Sample'[UserID]) >=4, COUNTROWS('Sample'),0) would always returns FALSE if you use both Language and Stores as filter context, since the max discount users number is 2. That's why it doesn't display anything on stacked bar chart

[DataGeek16]

There should be a way to restrict the grouping to only the columns I want to include for the widget. I see a bunch of DAX commands but unsure of how to use this conditional filtering. 

[popov]

Hello, @DataGeek16

If i correct understud, you can try this formula

MSR_UsersandStores =
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Sample'[UserID] ), ALL ( 'Sample'[Stores] ) ) >= 4,
    COUNTROWS ( 'Sample' )
)