Forum Discussion
Matching Logic Report
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.
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.
Is there a way to do this to just add a column in the same table and not have to create a new table?
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.Hi cingram11,
Just wanted to check if you had the opportunity to review the suggestion provided?If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Regards,
Harshitha.Hi cingram11,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.
Thank you.