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,

     

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Sha ,

 

Sorry for that we forgot to put the sample pbix file in our previous post. The above visual comes from the origin table and the below is come from the copied table, we only applied the visual filter in the below visual. We did not set relationship between the two tables.


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!