Forum Discussion

jmckentr's avatar
jmckentr
Frequent Visitor
9 years ago
Solved

Multiple Filter Application for Totals

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 inb...
Tutorial Requests
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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.

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago
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's avatar
    jmckentr
    Frequent Visitor
    9 years ago

    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's avatar
      Vvelarde
      Community Champion
      9 years ago

      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.