Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowGet certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started
Table 1:
Date | Item | Sales Amount |
10/01/2021 | Apple | 300 |
10/02/2022 | Orange | 700 |
20/03/2022 | Carrot | 300 |
21/03/2022 | Onion | 800 |
Table 2:
Date | Item | Category | Sales Amount |
10/01/2021 | Apple | Fruits | 300 |
20/2/2022 | Carrot | Vegetable | 800 |
10/02/2022 | Orange | Fruits | 700 |
21/03/2022 | Onion | Vegetable | 200 |
Relationship: Table1[Item] and Table2[Item] are connect using Many to many relationship.
Im trying to plot three lines of graph for table 1.
Line 1- Represents the maximum sale amount based on its category(category value retrieved from selected value of item displayed in slicer)
Line 2 – Represents the sale amount of selected item over the date.
Line 3 -- Represents the minimum sale amount based on its category(category value retrieved from selected value of item displayed in slicer)
My Problem is
Unable to plot the maximum/minimum sale amount line based on the slicer item category.
When you change the item in the slicer, the maximum/minimum line chart must be updated based on the selected item category, but in this case, the lines change based on the selected item value rather than the max/min value.
My Question is:
How to plot the maximum/minimum value chart only based selected item’s category.
Example :
If I choose the apple in slicer, the maximum/minimum plot will show the maximum and minimum value of the fruits category.
@amitchandak could you please look into this
Solved! Go to Solution.
Try the following change:
Maximum =
VAR CategorySelection =
VALUES( TableDimension[Category] )
RETURN
CALCULATE (
MAX ( Table[Sales] ),
FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Maggi029 ,
Try to change the metric of the max/min to somwething similar to this:
Maximum =
VAR CategorySelection =
VALUES( TableDimension[Category] )
var ItemValues = CALCULATE (
DISTINCTCOUNT ( Table[Item] ),
FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
)
RETURN
IF(ItemValues > 1,
CALCULATE (
MAX ( Table[Sales] ),
FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
)
)
This should return blank for the maximum and minimum when there is only a single value so the labels will not appear.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix
I need your suggestion for below query , which is also related to this same data table
How to find the maximum value of a measure in tabl... - Microsoft Power BI Community
Hi @Maggi029 ,
I believe that you need to create two dimension tables onbe for dates and another for the items to make your relationships between the tables.
However I have one question you have sales on both tables what is the sales column you want to see on the chart? Is it the values from table1, table 2 or both?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for your response,
I have made the releationship as suggested, but the real problem here how to wrote measure to plote the max/min for selected Item Category.
For your question:
It is just a replicate of original data, so table 2 has separate reports in my project
Hi @Maggi029 ,
If the values are the same and you create a dimension table with the items/Category then you can create a similar metric to have the maximum or minimum:
Maximum =
VAR CategorySelection =
SELECTEDVALUE ( TableDimension[Category] )
RETURN
CALCULATE (
MAX ( Table[Sales] ),
FILTER ( ALL ( TableDimension ), TableDimension[Category] = CategorySelection )
)
Be aware that I'm doing this by heart.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for response, You measure is working fine, but I have one special case
Case : Because one of the items belongs to two categories, "SELECTEDVALUE" does not return the expected value because it has two values.
Can suggest how to handle above case?
Try the following change:
Maximum =
VAR CategorySelection =
VALUES( TableDimension[Category] )
RETURN
CALCULATE (
MAX ( Table[Sales] ),
FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for for you help, your solution working fine.
Also i have one more query related to this, if a category has only one value, the Maximum and Minimum data points will be the same, making the group look crowded with the same value repeated when Data label is enabled. How can this be limited in this case by showing only one data label?
Hi @Maggi029 ,
Try to change the metric of the max/min to somwething similar to this:
Maximum =
VAR CategorySelection =
VALUES( TableDimension[Category] )
var ItemValues = CALCULATE (
DISTINCTCOUNT ( Table[Item] ),
FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
)
RETURN
IF(ItemValues > 1,
CALCULATE (
MAX ( Table[Sales] ),
FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
)
)
This should return blank for the maximum and minimum when there is only a single value so the labels will not appear.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
How to find the Item name which has max value in above measure?
Note: Above measure returen maximum item values but i need item name.
Hi @Maggi029 ,
What do you mean find the category name?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
I mean , below code, filter the values in categoryselection
FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection )
i need to get the selected value name when using above filter
Hi @Maggi029
You want to get the values of the selection of the Categories that are selected?
Try the following:
Selected Categories =
CONCATANEX ( VALUES( TableDimension[Category] ), TableDimension[Category] , ",")
this will concatenate the selected values into a list with comma has separator.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Can you please look into this and provide your suggestion for this
How to find the maximum value of a measure in tabl... - Microsoft Power BI Community
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |