Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sha
Helper II
Helper II

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 by those shared clients.  Any help would be appreciated.

 

 

2 ACCEPTED SOLUTIONS
v-lid-msft
Community Support
Community Support

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
)

 

5.jpg6.jpg

 

 

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


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-lid-msft
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
az38
Community Champion
Community Champion

hi @Sha 

it could be smth like measure

Measure = 
CALCULATE(
SUM('Table1'[Sales]);
FILTER(ALL('Table1';'Table1'[Client]=SELECTEDVALUE('Table1'[Client]) && 'Table1'[Business Unit]<>SELECTEDVALUE('Table1'[Business Unit]))
)

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

That didn't work, it just returns my selected business unit's sales.  I need to get all the Clients that also have sales in other business units.  Expecting to see something like this.  If I'm comparing to Bus unit 3, I would see something like this...

Client       Bus unit                      Sales

Name1    Bus Unit1                  $xxx

Name1    Bus Unit2                  $xxx

Name1    Bus Unit4                  $xxx

Name2    Bus Unit4                  $xxx

Name2    Bus Unit4                  $xxx

 

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-lid-msft
Community Support
Community Support

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
)

 

5.jpg6.jpg

 

 

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


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I thought that was working.  But mine is doing something yours is not.  Mine is showing the selected value in the totals.  Your slicer is from Table? Top visual is where HasSameCliet = -1 and Bottom visual is where = 1?  Should I have a relationship in the model for the new table?

The other thing that I have different, is I a have a date in Table and related to a date table.  Perhaps I need to relate the new table to the date table or change the measure?

 

Thank you for you help.

v-lid-msft
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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])))

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
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Perfect, thank you so much!

Thank you for your reply and help.

Top visual will include all Sales whether or not related to same client.  I need to show only those related.

Bottom visual is great for the detail.

I'm now looking to roll that up to a summary, but when I change Bus Unit to distinct count and Sales to Sum, they now include the Bus Unit selected and  I don't want that.

 

 

Thank you so much, that gets me what I need. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.