Matching Logic Report

[cingram11]

I want to create a report that would show, for example, Item #s that are purchased by both Restaurant A and Restaurant B. How would I implement this? I currently have a table, in table view, with a column of the brand and a column of item #s. I just want to create a table in report view to show only matching item #s. 

For example, in the image below... I would want to show the matching item #s 200 and 300 in a visual report table.

[danextian]

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[Syndicate_Admin]

Hello

I hope I have understood your question and if not, let me know.

In this case I am going to represent it in Restaurant (A, B, C) and the next column would be ArticleID. I attach a photo of how it could be represented in your report. I don't know if you can attach a GDP file. Luck

[v-hjannapu]

Hi @cingram11,
Thank you  for reaching out to the Microsoft fabric community forum.
you can achieve this by creating a calculated table that filters Item #s purchased by both Restaurant A and B.
If you use this DAX, you will get the result you expected.

CommonItems_AB = 
VAR TargetBrands = {"Restaurant A", "Restaurant B"}
RETURN
FILTER (
    VALUES (SalesData[Item #]),
    CALCULATE (
        DISTINCTCOUNT (SalesData[Brand] ),
        SalesData[Brand] IN TargetBrands
    ) = 2
)

I tested it with my sample data, and it worked fine. Please find the attached Pbix for your reference.
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated.

Thank you,
Harshitha.

[cingram11]

Is there a way to do this to just add a column in the same table and not have to create a new table?

[v-hjannapu]

Hi @cingram11,

Yes, you can do this without creating a new table. You just need to add a calculated column in your existing table that checks if the item is bought by both Restaurant A and B.
Dax you can use :

CommonItemFlag = 
VAR TargetBrands = {"Restaurant A", "Restaurant B"}
VAR BrandCount =
    CALCULATE (
        DISTINCTCOUNT(SalesData[Brand]),
        FILTER (
            SalesData,
            SalesData[Item #] = EARLIER(SalesData[Item #]) &&
            SalesData[Brand] IN TargetBrands
        )
    )
RETURN
IF(BrandCount = 2, 1, 0)

This approach will return a value of 1 for items that are shared between the two restaurants. You can then filter this column in your report to display only these common items.
I hope this information is helpful.

If the answer is helpful, please click "Accept Answer" and feel free to give a Kudos, so others can benefit as well.
Best Regards,
Harshitha.

[ryan_mayu]

@cingram11 

pls provide some sample data and expected output