Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gaiusgw
Helper III
Helper III

Identify matching results

Not sure how to go about this... 

 

Sometimes I need to know when customers have ordered the same items so I can let the customers know and avoid a conflict. 

 

I would like to be able to choose 2 customers from a slicer and have a table result show the items that both of them have on order. 

 

Also, I would need the option of identifying these conflicts by item and by color becuase in some cases, if the customer has a different color, then it might not be an issue. 

 

I am not sure what the best approach would be to generate this result. Appreciate any advise. Thanks. 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @gaiusgw ,

Measure change to this:

test = IF(CALCULATE(DISTINCTCOUNT('CUSTMAST'[CUSTNAME]),FILTER(ALLSELECTED('OPENDET'),'OPENDET'[Color]=MAX('OPENDET'[Color])&&'OPENDET'[Style]=MAX('OPENDET'[Style])))>=2,"#FF0000","#000000")

 

before chage:

v-luwang-msft_0-1619664174649.png

 

after chage:

v-luwang-msft_1-1619664198598.png

 

 

Best Regards

Lucien

View solution in original post

9 REPLIES 9
v-luwang-msft
Community Support
Community Support

Hi @gaiusgw ,

Measure change to this:

test = IF(CALCULATE(DISTINCTCOUNT('CUSTMAST'[CUSTNAME]),FILTER(ALLSELECTED('OPENDET'),'OPENDET'[Color]=MAX('OPENDET'[Color])&&'OPENDET'[Style]=MAX('OPENDET'[Style])))>=2,"#FF0000","#000000")

 

before chage:

v-luwang-msft_0-1619664174649.png

 

after chage:

v-luwang-msft_1-1619664198598.png

 

 

Best Regards

Lucien

@v-luwang-msft Yes! That did it. Thanks so much Lucien. 🙂 

v-luwang-msft
Community Support
Community Support

Hi @gaiusgw ,

The following is my pbix file .Could you pls share your pbix file ,remember to remove confident data.

 

Best Regards

Lucien

@v-luwang-msft Hey Lucien, I started a new pbix from scratch with dumby data to see if I could replicate the issue. The issue seems to be that your formula somehow is not taking the slicer into consideration. See screenshot below where I only have 2 accounts selected but formatting changes because other unselected accounts have the style on order. Hopefully this makes sense. I have put an arrow next to the ones that should not have been formatted. When applied to my original pbix, I end up with all red formatting because there are other customers that have the styles/colors on order that are not selected. Hope this makes it clear. Here is a link to download the pbix and source files. https://we.tl/t-BQkM2094uM. Thanks again for looking into this. 

 

Capture.JPG

 

 

v-luwang-msft
Community Support
Community Support

Hi @gaiusgw  ,

You could use the following Steps:

Step1, Use the following measure:

test = IF(CALCULATE(DISTINCTCOUNT('Table'[CUSTNAME]),FILTER(ALL('Table'),'Table'[Color]=MAX('Table'[Color])&&'Table'[Style]=MAX('Table'[Style])))>=2,"#FF0000","#000000")

 

Step 2,configure color:

v-luwang-msft_0-1619505633475.png

v-luwang-msft_1-1619505645791.png

 

Final you will see the below:

v-luwang-msft_2-1619505707317.png

 

Wish it is helpful for you!

 

Best Regards

Lucien

 

Hey Lucien,

Thanks for the help. I feel like this is really close but for some reason I am getting a result where everything is red... Here is a full screenshot so hopefully you can see where this might have gone wrong. The customer name comes from a different table than the style and color data so maybe this is the issue? 

 

Capture.JPG

 

FrankAT
Community Champion
Community Champion

Hi @gaiusgw ,

you can do it with your sample data in Power Query like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZTJboMwEIZfJeIMknebI1vUxQkoIYeK5hApHCpVPVRqn79jIMFQMOGAZaT5/tldVZ6u68/L5v0HISI2+vJbf13rb8/34leMhOTmBh8mcBCqvLNfedn1o24sCi6kMDcdJNEuj/Y7+GHK2PuYkcZ4Vr8E/ZB2+hTBIdEjCBsgii4gfOyECzuJJFWYGclE97HzEE/a5BM2c56HUOMa+4i7w71Dx3wFtC0wom2WQRE9H3X2Ns7WwUKBwikU+5TQQRk0+JG49QPtPh36alAlbdM0U5hgw1gTIaQ7jyHUxQAex8LMjFy6XyNcdMp2LwjmQ+WyM4qDMu3FFXVX8B83kp/jtpoQFtqTCVWU0s2U0AEu6ciZYIO9BKvbXlqZhDJ0aj/BchHK7YCYcqfeI/24PsIISNNywxmyw3+BZjHz3MSHoLD6sFCbMXZrw8IzdMfWU7R9+SysWZjzHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CUSTNAME = _t, Style = _t, Color = _t, Quantity = _t, #"Current Sales" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CUSTNAME", type text}, {"Style", type text}, {"Color", type text}, {"Quantity", Int64.Type}, {"Current Sales", type number}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Style", "Color"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Grouping", each _, type table [CUSTNAME=nullable text, Merged=text, Quantity=nullable number, Current Sales=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Count Rows", each Table.RowCount([Grouping])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Count Rows] = 2)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Count Rows"}),
    #"Expanded Grouping" = Table.ExpandTableColumn(#"Removed Columns", "Grouping", {"CUSTNAME", "Merged", "Quantity", "Current Sales"}, {"CUSTNAME", "Merged.1", "Quantity", "Current Sales"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Grouping",{"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged.1", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Style", "Color"})
in
    #"Split Column by Delimiter"

 20-04-_2021_22-50-07.png

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Thanks @FrankAT. But I am not quite sure how to plug this in... I do not want to adjust the source tables because they are being used by other visuals. 

 

Here are the sources:

 

CUSTMAST[CUSTNAME]

OPENDET[STYLE]

OPENDET[COLOR]

OPENDET[ORIG_QTY]

 

Your solutions involes creating a new table correct? 

gaiusgw
Helper III
Helper III

It would be nice if it only showed the conflicting style/color but this could be an option too if it could display the matches in highlights so it is clear.  

Capture.JPG

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.