Filter transactions based on various states with exclusive results

[amcle]

Hi All, 

Been stuck on the logic on how to correctly filter this data. Essentially I am looking to filter on a transaction list I have comprising of customers, states and volume. However based on the selected states, I want to filter customers who have purchased in the selected states only. This means if this customer purchases in another state out of my selected values they won't be included. 

Please see an example below

Company Name	State	Litres
Company A	NSW	100
Company A	ACT	100
Company A	VIC	100
Company A	QLD	100
Company A	NT	100
Company B	NSW	100
Company B	VIC	100
Company C	NSW	100
Company D	VIC	100

If I filtered on the states: NSW and VIC the output would be like this:

Company Name	State	Litres
Company B	NSW	100
Company B	VIC	100
Company C	NSW	100
Company D	VIC	100

As you can see Company A isn't filtered because that company has volume in NT.

I was thinking of creating a measure to return 1,0 but struggling to find the logic to write it. Thanks!

[amcle]

@NaveenGandhi Thanks so much for your response!!!
When I applied your solution to a bigger data set with more state selections than just NSW and VIC, it appears to error. 

For example, I have increased my sample table below:

Company Name	State	Litres
Company A	NSW	100
Company A	ACT	100
Company A	VIC	100
Company A	QLD	100
Company A	NT	100
Company B	NSW	100
Company B	VIC	100
Company C	NSW	100
Company D	VIC	100
Company E	QLD	100
Company E	NSW	100
Company E	VIC	100
Company E	ACT	100
Company F	VIC	100
Company F	TAS	100
Company G	VIC	100

When I select NSW, ACT,VIC,QLD,NT... I expect Company A to filter a 1 but instead it gives Company C as an output.

How would I fix this issue?

The real data set I'm working with is much larger and will have 8 states to select from 

[NaveenGandhi]

@amcle 

For the same dataset and slicer selection, i get below result, As per the requested logic only company F should be excluded as Company F has state TAS apart from the selected states.

Let me know if the logic needs to be further tweaked.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[amcle]

@NaveenGandhi Hi, I'm so sorry I didn't word my initial post correctly I just realised and I've been posting wrong. Let me try and correct myself.

The tricky part with what I'm trying to achieve is that it has to satisfy the following conditions:

1. If a company has litres in all selected states, it includes that company.

2. If a company has litres in only one selected state, it includes that company.

3. If a company has litres not in a selected state, it excludes that company.

Using the table below:

Company Name State Litres

Company A NSW 100

Company A ACT 100

Company A VIC 100

Company A QLD 100

Company A NT 100

Company B NSW 100

Company B VIC 100

Company C NSW 100

Company D VIC 100

Company E QLD 100

Company E NSW 100

Company E VIC 100

Company E ACT 100

Company F VIC 100

Company F TAS 100

Company G VIC 100

Therefore, if I filtered on NSW, ACT, VIC and QLD expect the following result:

Company Name	State	Litres
Company B	NSW	100
Company B	VIC	100
Company C	NSW	100
Company D	VIC	100
Company E	QLD	100
Company E	NSW	100
Company E	VIC	100
Company E	ACT	100
Company F	VIC	100
Company G	VIC	100

I apologise I wasn't explaining it properly before, hope this helps explain why I'm struggling to find a logic that works for those scenarios.

[NaveenGandhi]

@amcle 

Here is a more simple solution. Create Dimension table for state, create relationship and use it as the slicer. Then create below measure, use it on Visual filter as earlier.

Filter_New =
VAR C1 =
CALCULATE (
COUNTROWS ( 'Company Filter' ),
REMOVEFILTERS ( 'Company Filter'[State] )
)
VAR C2 =
CALCULATE (
COUNT ( 'Company Filter'[Company Name] ),
ALLEXCEPT ( 'Company Filter', 'Company Filter'[Company Name] )
)
RETURN
IF (
NOT ( ISBLANK ( MAX ( 'Company Filter'[State] ) ) ),
IF (
COUNTROWS ( Dim_State ) = 1,
1,
IF (
AND ( c1 = 1, c2 - c1 = 1 ),
1,
IF ( AND ( c1 <> 1, c2 - c1 = 1 ), 0, IF ( c2 = c1, 1, c2 - c1 ) )
)
)
)

Let me know if this works!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[NaveenGandhi]

Hello @amcle 

Try the below solution. First create a disconnected table using below dax.

Dim_com_state = SUMMARIZE('Company Filter','Company Filter'[Company Name],'Company Filter'[State])

Then create this measure and place in the visual filter and make it is "1".

Filter_1 =
VAR t1 =
CALCULATE (
CONCATENATEX (
SUMMARIZE (
FILTER (
'Dim_com_state',
NOT ( 'Dim_com_state'[State] IN VALUES ( 'Company Filter'[State] ) )
),
Dim_com_state[Company Name]
),
Dim_com_state[Company Name],
" "
),
ALLSELECTED ( 'Company Filter' )
)
RETURN
IF ( CONTAINSSTRING ( t1, MAX ( 'Company Filter'[Company Name] ) ), 0, 1 )

Let me know if this helps.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!