topic Re: Compare, evaluate, and visualize “mismatched” data across a table in DAX Commands and Tips

Compare, evaluate, and visualize “mismatched” data across a table

PortoIvan — Thu, 20 Oct 2022 23:27:19 GMT

Hello!

I have been trying for weeks to figure out the best way to determine then visualize the occurrence of what I call mismatched data within a table.

consider my sample data below in the following layout:

Date	Member	Vendor	SKU	Category
9/22/22	A	Homemade	11-110	Price Match
9/20/22	A	Homemade	11-110	No Price Found
9/12/22	B	Wooden	23-1111	Price Match
8/20/22	C	Homemade	000-245	Price Match
8/15/22	B	Wooden	23-1111	No Price Found
7/10/22	B	Wooden	23-1111	No Price Found
7/02/22	A	Homemade	11-110	Price Match
7/01/22	C	Homemade	000-245	Price Match

I am trying to create a measure or configure the table above so that for each Member, Vendor, and SKU, the resulting category is compared to previous categories of the same Member, Vendor, and SKU.

For example, Member A purchased item 11-110 from the same vendor on 3 different occasions. 2 of those occasions resulted in a category of Price Match but 1 of those purchases resulted in a category of No Price Found. I would like the purchase that resulted in the "mismatch" to be displayed in the table or visual.

Doing this will help me track, prioritize work, and highlight category mismatches that occur.

I appreciate any help!

Re: Compare, evaluate, and visualize “mismatched” data across a table

Greg_Deckler — Thu, 20 Oct 2022 23:22:07 GMT

PortoIvan Couldn't you just use the Filters pane to remove any "Price Match" for Category?

Re: Compare, evaluate, and visualize “mismatched” data across a table

PortoIvan — Thu, 20 Oct 2022 23:41:47 GMT

Greg_Deckler Unfortunately no, because although that would work for one member for another member a category of "Price Match" might not be the "mismatch". For example using the same table I provided Member B ordered Item 23-111 multiple times, however in their case the mismatch is the 1 purchase they made where the item had a category of Price Match, since No Price Found occurred twice where as Price Match occurred once.

Re: Compare, evaluate, and visualize “mismatched” data across a table

Greg_Deckler — Thu, 20 Oct 2022 23:36:45 GMT

PortoIvan Did you mean that for B that the mismatch is "Price Found"? If that is the case, then you could do this:

Complex Selector = VAR __Member = MAX('Table'[Member ]) VAR __Category = MAX('Table'[Category ]) VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Member ] = __Member),[Category ],"__Count",COUNTROWS('Table')) VAR __Min = MINX(__Table,[__Count]) VAR __MinCategory = MINX(FILTER(__Table, [__Count] = __Min),[Category ]) RETURN IF(__Category = __MinCategory,1,0)

Re: Compare, evaluate, and visualize “mismatched” data across a table

PortoIvan — Thu, 20 Oct 2022 23:44:07 GMT

Greg_Deckleryour correct I meant the mismatched occurred where the category for one of the three purchases made by member B was "Price Match" I edited my previous reply.

Re: Compare, evaluate, and visualize “mismatched” data across a table

Greg_Deckler — Fri, 21 Oct 2022 00:13:16 GMT

PortoIvan I edited my reply also with what I believe is the solution.

Re: Compare, evaluate, and visualize “mismatched” data across a table

PortoIvan — Fri, 21 Oct 2022 13:03:56 GMT

You're amazing! Thank you! This solved my problem!!