Multiple Filter Application for Totals

[jmckentr]

How would I be able to filter all Detriot inbound and outbound results for a Yes compliance? If I select to filter detroit on inbound it ignores outbound detroit values because there were not any inbound shipments going from the same place.

My end result would be Detroit: total compliance = 3.

I would, in the end, have to find total compliance for each location.

Inbound	Outbound	Comliant
Detriot	New York	Yes
Detriot	New York	No
Detriot	New York	Yes
Detriot	New York	No
Detriot	New York	No
Cincinnati	New York	No
Cincinnati	Las Vegas	Yes
Cincinnati	Las Vegas	Yes
Cincinnati	Detriot	Yes
Cincinnati	Detriot	No
Cincinnati	Houston	No
Cincinnati	Houston	Yes
Cincinnati	Tampa	Yes
New York	Houston	Yes
New York	Tampa	No
New York	New Jersey	No
New York	Toronto	No

[vanessafvg]

@jmckentr
using intersect 
here is an example (would need to be reworked)

Plum/Elect =
COUNTROWS (
    INTERSECT (
        CALCULATETABLE (
            DISTINCT ( 'Completed Repairs'[STREET_NAME] ),
            'Completed Repairs'[TradeCode] = "Elec"
        ),
        CALCULATETABLE (
            DISTINCT ( 'Completed Repairs'[STREET_NAME] ),
            'Completed Repairs'[TradeCode] = "Plum"
        )
    )
)

[jmckentr]

Is there anyway to have that connected with a slicer? That way tthe viewer can select which location and the report will display the number of yes complaint results.

[Vvelarde · Accepted solution]

@jmckentr

Hi, One way to solve this:

1. Create a New Table with the Cities. (Modeling-New Table)

Cities = DISTINCT(UNION(VALUES(Table1[Inbound]),VALUES(Table1[Outbound])))

2. Related Both Tables: (Inbound & Outbound with Cities)

Both with Cities

3. Create a measure to Count.

TotalCompliants =
CALCULATE (
    COUNT ( Table1[Inbound] ),
    USERELATIONSHIP ( Cities[Cities], Table1[Inbound] )
)
    + CALCULATE (
        COUNT ( Table1[Outbound] ),
        USERELATIONSHIP ( Cities[Cities], Table1[Outbound] )
    )

Insert a Slicer with the cities and also a slicer to  Compliants. (or in a Page Filter)

 Ready.