Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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:
after chage:
Best Regards
Lucien
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:
after chage:
Best Regards
Lucien
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.
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:
Final you will see the below:
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?
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"
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?
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |