Show all items in an order when one product is filtered

[elcheffe]

My dataset contains sales data from customers.
I want to create a table with the following REQUIREMENTS:

in the columns are all invoice numbers
In the rows are the services that the customers have booked.
The value in the table shows how often a service was booked in an invoice number (e.g. 2 times product A = 2)

It should also be possible to search for a specific service using the visual search bar.
Then only the invoice numbers should be displayed , that has the searched service in the columns containing the service.

And now the problem:
I would also like to display all other services that are booked together with the searched service, whereby they should be in the same invoice number.

I want to have it like this:

Example: I search for: Service A

The first column shows me that Service A has been booked with Service B, but not Service C. So then, Service A:1 ; Service B:1 ; Service C: 0

The second column shows that Service A and C have been booked, i.e.: Service A:1 ; Service B:0 ; Service C: 1

How do I set up a table like this? I want to see which services are booked together and which are not, while searching for a specific service. My problem is that after I filter for Service A, it only shows rows with Service A.

[elcheffe]

okay Can you give me an example?

o I did create a copy of the data and also made following measure:

ServiceCount =
VAR SelectedService = SELECTEDVALUE(ServiceSlicer[Service])
RETURN
CALCULATE(
SUM(MainData[LeistungMedikamentArtikelMenge]),
REMOVEFILTERS(MainData[LeistungMedikamentArtikelBezeichnung]),
FILTER(
ALLSELECTED(MainData),
CALCULATE(
SUM(MainData[LeistungMedikamentArtikelMenge]),
MainData[LeistungMedikamentArtikelBezeichnung] = SelectedService
)
)
)
whereas MainData[LeistungMedikamentArtikelBezeichnung] is the name of the service,
MainData[LeistungMedikamentArtikelMenge] is the amount of how often the service was purchased by specific customer (in invoice),
MainData[BehandlungRechnungsnummer] is the invoice number ( this I have not used in this measure).
ServiceSlicer[Service] is basically MainData[LeistungMedikamentArtikelBezeichnung] but in a copy of the MainData Table which is not being connected to MainData.

Unfortunatly I can not come to a result. Maybe you would have an idea how to construct a measure.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[elcheffe]

Hey I uploaded a test file in a drive, which is simplified but maybe you can share me the logic/measure:
https://drive.google.com/file/d/1YHIq0UbRAZV6eGUjCP_QDcT5PJq5sv8g/view?usp=sharing

The table now looks like this: 

and if I choose a specific kind of service lets say AA, then it should show invoice 1 (and all other invoices with AA)and it should show me all other services booked in the invoice. so AA and AB in invoice 1.
Now I only have AA, like this: 

I appreciate any kind of help.

[lbendlin · Accepted solution]

see attached.  Make sure your users understand what is happening.

[elcheffe]

Thank you so much for the code. I inspected it now, with the help of ChatGPT. I have one question.

Show = 
VAR sers = 
    ADDCOLUMNS(
        VALUES('Invoice Sells'[Invoicenumer]),
        "s", CALCULATE(SUM('Invoice Sells'[Service Sells])),
        "i",
        VAR inv = [Invoicenumer]
        RETURN IF(
            CALCULATE(
                COUNTROWS('Invoice Sells'),
                TREATAS(VALUES(Services[Service]), 'Invoice Sells'[Service]),
                'Invoice Sells'[Invoicenumer] = inv
            ) > 0, 1
        )
    )
RETURN SUMX(sers, [i] * [s])

I never really used the TREATAS() function. 
Does this part of the DAX Measure:

TREATAS(VALUES(Services[Service]), 'Invoice Sells'[Service]),

make sure that in the table, all Services with the correct invoice are getting shown? Is this function the crucial part of the measure so the filtering works?
Because I saw that the tables were not connected to each other, but the filtering still works. 

Anyway thank you very much for the help. I would have never come up with that solution by myself 😛

[lbendlin]

TREATAS allows you to transfer the filter context from one table to another even if they are not directly connected in the data model. It takes some practice to get used to it but you should consider it to be your primary filtering option. It is used a lot behind the scenes in visual formulas.

[lbendlin]

That's a typical "filtering up"  pattern.  If you only want to compute the siblings you can use measures with REMOVEFILTERS.  If you want to display the siblings you need to use a disconnected copy of your data for the slicer.