Forum Discussion

Sha's avatar
Sha
Icon for Helper II rankHelper II
6 years ago
Solved

How to get common data from selected value in slicer

 I have one table that contains business unit, client, sales.  I am trying to figure out if my selected business unit (slicer) shares the same client with other business units and get total of sales ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Sha ,

     

    First of all, we can create a separate table to show the data, then we can use a measure in visual filter to filter it:

     

    TableToCompare = 'Table'

     

    HasSameClient = 
IF (
    AND (
        SELECTEDVALUE ( 'TableToCompare'[Client] ) IN DISTINCT ( 'Table'[Client] ),
        NOT SELECTEDVALUE ( TableToCompare[Bus unit] ) IN FILTERS ( 'Table'[Bus unit] )
    ),
    1,
    -1
)

     

     

     

    If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Sha ,

     

    We can use the variable to optimise the code, if you have any other questions, please kindly ask here and we will try to resolve it.

     

    # of Bus Units =
VAR SelectedBus =
    SELECTEDVALUE ( 'Table'[Bus unit] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'TableToCompare'[Bus unit] ),
        'TableToCompare'[Bus unit] <> SelectedBus
    )

     


    Best regards,

     

Sha's avatar
Sha
Icon for Helper II rankHelper II
6 years ago

I have over 400,000 rows and the below measure is taking over 5 minutes to perform but I think I have solved for returning counts the way I would like to see them.  Any help to optimize would be appreciated.

# of Clients = CALCULATE(DISTINCTCOUNT('Table'[Client]),filter(TableToCompare,TableToCompare[Bus unit]<> SELECTEDVALUE(TableToCompare[Bus unit])))

Sha's avatar
Sha
Icon for Helper II rankHelper II
6 years ago

Wrote previous measure incorrectly...

corrected...

# of Bus Units = CALCULATE(DISTINCTCOUNT('TableToCompare'[Bus unit]),filter(TableToCompare,TableToCompare[Bus unit]<> SELECTEDVALUE('Table'[Bus unit])))
  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Sha ,

     

    We can use the variable to optimise the code, if you have any other questions, please kindly ask here and we will try to resolve it.

     

    # of Bus Units =
VAR SelectedBus =
    SELECTEDVALUE ( 'Table'[Bus unit] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'TableToCompare'[Bus unit] ),
        'TableToCompare'[Bus unit] <> SelectedBus
    )

     


    Best regards,

     

  • Sha's avatar
    Sha
    Icon for Helper II rankHelper II
    6 years ago

    Perfect, thank you so much!