Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
So I have some simple data, please see the below example:
Date | Seller Name | Item Number | Price |
Oct 26 | Jill | 123ABC | 10 |
Oct 26 | Bob | 123ABC | 9 |
Oct 26 | Jill | 456DEF | 20 |
Oct 26 | Joe | 456DEF | 20 |
Oct 27 | Bob | 123ABC | 9 |
Oct 27 | Jill | 123ABC | 10 |
Oct 27 | Joe | 456DEF | 19 |
Oct 27 | Bob | 456DEF | 18 |
I want to return the Seller name associated with the lowest Price for each combination of Item Number and Date in a stacked column chart or in a table. I have a slicer on my dashboard for the Item Number so only one Item Number can be selected at a time. My chart or table will use the date for each column. Please see the below example bar chart.
This seems so simple. The date is segregated by the chart's x-axis and the item number is isolated by the slicer but I cannot figure out how to return the SellerName associated with the lowest price for each date/item number combo.
Solved! Go to Solution.
@Frenchtom811 Seems like a variation on lookup min/max like:
Measure =
VAR __Table = SUMMARIZE('Table',[Date],[Seller Name],[Item Number],"__Price",MIN([Price])
VAR __Min = MINX(__Table,[__Price])
RETURN
MAXX(FITLER(__Table,[__Price]=__Min),[Seller Name])
@Frenchtom811 Use CONCATENATEX instead of MAXX
@Frenchtom811 Seems like a variation on lookup min/max like:
Measure =
VAR __Table = SUMMARIZE('Table',[Date],[Seller Name],[Item Number],"__Price",MIN([Price])
VAR __Min = MINX(__Table,[__Price])
RETURN
MAXX(FITLER(__Table,[__Price]=__Min),[Seller Name])
Hello @Greg_Deckler and thanks for your response. I got your calculation to work in a table which is good enough for me. Do you know how I can return the names of multiple sellers when they have the same minimum price? Referring to my above example on 26 Oct for item number 456DEF, I want to display Joe and Jill. Right now, the measure displays Joe or Jill. Thanks!
@Frenchtom811 Use CONCATENATEX instead of MAXX
User | Count |
---|---|
99 | |
87 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |