Clients of the selected seller at other sellers

[lizab]

Hi all,

I have tried to search through the community and asked AI, but I cant find the solution, so I hope one of you can help me!

I have data with Client ID, Seller ID, Product ID, StartDate and EndDate of the products. What I am looking for is the following: there will be a slicer with Seller ID and slicers with StartDate and EndDate. Based on the selected values I want to see per Product ID and Seller ID how many distinct Client ID's there were. So if for example Seller ID 1 is selected, i want to see per product A, B, C how many Clients there were at Sellers 1,2,3 BUT they have to be a client at 1 at the selected moment. This has to be dynamic so I can't just use a table.

I hope I am overlooking something and somebody can help me!

[v-jtian-msft]

Hello,@miTutorials,thanks for your concern about this issue.
And I would like to share some additional solutions below.
Hi,@lizab . I am glad to help you.
Please check to see if this will meet your needs:

To avoid direct filtering of the data when using the seller ID in the table as a slicer (you want to show all the data), I created a separate table for slicer filtering
Slicer Table:

M_result =
VAR _slicer =
    VALUES ( 'Slicer_SellerID'[Seller ID] )
VAR _table =
    CALCULATETABLE (
        VALUES ( Sales[Client ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Seller ID] IN _slicer )
    )
VAR result =
    IF (
        ISFILTERED ( Slicer_SellerID[Seller ID] ),
        IF ( MAX ( 'Sales'[Client ID] ) IN _table, 1, 0 ),
        0
    )
RETURN
    result

Conditional formatting of columns based on the value of measure

The measure also supports slicer multi-selection (you can check multiple Seller IDs at the same time)

This is my test data (copying the test data you provided)

Note that you need to take into account your filtering criteria and data structure in a real environment, and may need to modify the MEASURES in accordance with your real data, as the values of the MEASURES are dynamically changed depending on the current computing environment
I have uploaded the test file, hope it can help you!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lizab]

Hi!

Thank you for your solution! This works well when there is table with Client ID's, but if I want to make bar chart, this doesn't seem to work, probably because it can't find Client ID to filter. Any ideas how to solve it? So those ID's that you highlighted with conditional color, I want to show them in a bar chart because the data I have is too big to view in a table. 

Any ideas are welcome and thatk you again!

[v-jtian-msft]

Hi,@lizab .Thank you for your reply.
Here is my test:

If you want to display the data in a bar chart instead of a table, according to the data you provided, the implementation of the slicer reverse filtering requires the use of Client ID, if you want to not place the [Client ID] column in the visual as a measure calculation, it is very difficult to realize your needs (you need to make sure that there is a column [Client ID] as a key filtering criterion in the visual (whether in a table, bar chart or any other visual). (you need to make sure that you have the column [Client ID] in your visual as a key filter, whether it is in a table, a bar chart or any other visual).

I created a test column as a result of the y-axis of the test
Like this:

Format visuals>Visual>Columns>color

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lizab]

Here is some fictional data. 

Imagine I choose slicer Seller ID 1, and we dont look at dates now, I want to see the highlighted data in my chart (these client were at some point a clients at Seller ID 1):

Does this help?

[miTutorials]

Please post sample data and expected output for us to help!