Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |