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.
I have been trying to use a few different ways to get the following output for the data model (see link below).
I'm having trouble traversing the model and aggregating appropriately.
I want to calculate the min, max and range for each combination of widget, source, metric name. And also have a value for the rank of that combination of items. Here is the kind of output i'm looking for.
Here is the PBIX: https://1drv.ms/u/s!Ap90MsT-kD-eg1jlS3wFzk7r0UEA?e=EbFcDY
Solved! Go to Solution.
Hi,@JohnKing
You can try the following methods, one is to calculate columns, the other is to measure.
MAX =
CALCULATE (
MAX ( 'Values'[MetricValue] ),
FILTER ( Widgets, Widgets[Widget Name] = EARLIER ( Widgets[Widget Name] ) )
)
MIN =
CALCULATE (
MIN ( 'Values'[MetricValue] ),
FILTER ( Widgets, Widgets[Widget Name] = EARLIER ( Widgets[Widget Name] ) )
)
Range =
[MAX] - [MIN]
Rank of Range =
IF (
[Range Measure] = BLANK (),
BLANK (),
RANKX ( 'Widgets', [Range Measure],, DESC )
)
The results are shown in the figure.
2. Measure
MAX Measure =
CALCULATE (
MAX ( 'Values'[MetricValue] ),
FILTER (
ALL ( 'Widgets-Sources' ),
[Widgets.Index] = MAX ( Widgets[Widgets.Index] )
)
)
MIN Measure =
CALCULATE (
MIN ( 'Values'[MetricValue] ),
FILTER (
ALL ( 'Widgets-Sources' ),
[Widgets.Index] = MIN ( Widgets[Widgets.Index] )
)
)
Range Measure =
[MAX Measure] - [MIN Measure]
Rank Measure =
IF (
[Range Measure] = BLANK (),
BLANK (),
RANKX ( ALL ( 'Widgets' ), [Range Measure],, DESC )
)
The results are shown in the figure.
Best Regards,
Community Support Team _Charlottez
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,@JohnKing
You can try the following methods, one is to calculate columns, the other is to measure.
MAX =
CALCULATE (
MAX ( 'Values'[MetricValue] ),
FILTER ( Widgets, Widgets[Widget Name] = EARLIER ( Widgets[Widget Name] ) )
)
MIN =
CALCULATE (
MIN ( 'Values'[MetricValue] ),
FILTER ( Widgets, Widgets[Widget Name] = EARLIER ( Widgets[Widget Name] ) )
)
Range =
[MAX] - [MIN]
Rank of Range =
IF (
[Range Measure] = BLANK (),
BLANK (),
RANKX ( 'Widgets', [Range Measure],, DESC )
)
The results are shown in the figure.
2. Measure
MAX Measure =
CALCULATE (
MAX ( 'Values'[MetricValue] ),
FILTER (
ALL ( 'Widgets-Sources' ),
[Widgets.Index] = MAX ( Widgets[Widgets.Index] )
)
)
MIN Measure =
CALCULATE (
MIN ( 'Values'[MetricValue] ),
FILTER (
ALL ( 'Widgets-Sources' ),
[Widgets.Index] = MIN ( Widgets[Widgets.Index] )
)
)
Range Measure =
[MAX Measure] - [MIN Measure]
Rank Measure =
IF (
[Range Measure] = BLANK (),
BLANK (),
RANKX ( ALL ( 'Widgets' ), [Range Measure],, DESC )
)
The results are shown in the figure.
Best Regards,
Community Support Team _Charlottez
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Amitchandak
This is close. For some reason the Rank is not quite getting picked up.
I have updated the pbix file in the original link above.
Also, I may submit a follow up question regarding having the min, max, and range values set up so that they are only calculated at that summarized level. That would allow me to get e.g., an average range rather than the range of whole set. I'll make that a new submission.
You need measures like
Min = min(Value[Metric Value])
Max = max(Value[Metric Value])
Range = [Max] - [Min]
Rank = Rankx(allelected(Values), [Range],,desc)
or
Rank = Rankx(summzrize(allselected(Values), Widget[WidgetName]), Label[Lable]), , [Range],,desc)
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |