Forum Discussion

tmears's avatar
tmears
Helper III
2 years ago
Solved

Multiple Slicer with AND Logic

I wonder if anyone can help, i have been going around in circles with this one for days.   I will try to explain as best as i can this is an example data table i have   i want to buil...
Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    tmears,

     

    Try this:

     

    1. Create calculated table (no relationships):

     

    SalesPersonSlicer = DISTINCT ( salesorder[Sales Person] )

     

    2. Create a slicer using SalesPersonSlicer.

     

    3. Create measure:

     

    OrdersDeliveredBySalespersonAndStage = 
VAR vOrderCounts =
    ADDCOLUMNS (
        VALUES ( salesorder[Order] ),
        "@RowCount", CALCULATE ( COUNTROWS ( salesorder ) ),
        "@RowCountSalesPerson",
            CALCULATE (
                COUNTROWS ( salesorder ),
                TREATAS ( VALUES ( SalesPersonSlicer[Sales Person] ), salesorder[Sales Person] )
            )
    )
VAR vResult =
    COUNTROWS ( FILTER ( vOrderCounts, [@RowCount] = [@RowCountSalesPerson] ) )
RETURN
    vResult

     

tmears's avatar
tmears
Helper III
2 years ago

thanks so much for your reply. not sure this will achieve what i am looking for.  I also need to filter by sales person.  basically i am trying to work as an example is  if i selected stage 1 and 2, how many orders individual sales people have done, when they were the sales person on both these stages.  if they are not the sales person on both stages, this data is exclude.  Form the highlight data if i selcted 1 and 2, it would retunr 1 if i had obviously on a normal slicer it will return if they were the sales person on either of these stages 

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    tmears,

     

    Try this:

     

    1. Create calculated table (no relationships):

     

    SalesPersonSlicer = DISTINCT ( salesorder[Sales Person] )

     

    2. Create a slicer using SalesPersonSlicer.

     

    3. Create measure:

     

    OrdersDeliveredBySalespersonAndStage = 
VAR vOrderCounts =
    ADDCOLUMNS (
        VALUES ( salesorder[Order] ),
        "@RowCount", CALCULATE ( COUNTROWS ( salesorder ) ),
        "@RowCountSalesPerson",
            CALCULATE (
                COUNTROWS ( salesorder ),
                TREATAS ( VALUES ( SalesPersonSlicer[Sales Person] ), salesorder[Sales Person] )
            )
    )
VAR vResult =
    COUNTROWS ( FILTER ( vOrderCounts, [@RowCount] = [@RowCountSalesPerson] ) )
RETURN
    vResult