The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi - I am using a live connection to my SSAS cube so creating columns is not an option. How can i create a measure that shows the maximum and minimum value of each column. In my image below the left is my powerbi matrix i created to replicate the already existing SSRS report i exported to excel (to the right). If you see to the right, this is what I am trying to achieve. I've been trying everything online and nothing has worked yet! Thanks in advance.
Solved! Go to Solution.
In general, let's say you have a table visual summarized by "Thing" and you have a measure in that table visual called "MySum", you can do this:
Max Measure =
VAR __Table =
SUMMARIZE(
'Table',
[Thing],
"__MySum", [MySum]
)
RETURN
MAXX(__Table,[__MySum])
So, generally if you want to find the minimum and maximums of columns you use MAX and MIN. If however you are trying to find the minimums and maximums of columns within visuals, you need to use MINX and MAXX. Within your measure, you create a table variable using VAR that emulates the aggregation within the visual and then use MINX and MAXX across that table to grab your min and max. Sort of like a measure totals/aggregation problem.
This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
Also, this Quick Measure, Measure Totals, The Final Word might get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Can you provide an actual DAX query or an example?
In general, let's say you have a table visual summarized by "Thing" and you have a measure in that table visual called "MySum", you can do this:
Max Measure =
VAR __Table =
SUMMARIZE(
'Table',
[Thing],
"__MySum", [MySum]
)
RETURN
MAXX(__Table,[__MySum])
Well, the links provide actual DAX examples. I can provide an actual example with your data if you can share some example/sample data that emulates your actual data.
User | Count |
---|---|
78 | |
73 | |
38 | |
30 | |
28 |
User | Count |
---|---|
107 | |
100 | |
55 | |
49 | |
45 |