Forum Discussion
MINX Measure Ignoring Slicer Filtering
The minimum of current cost measure below, works for the most part, but the minimum still pulls values from Community[_cCommunity with Number] and/or Options[_cOption Plan/Elev] combinations, that have been excluded via the slicers selections. Is there a way to rewrite the Cost Min measure to get this to work where the minimum cost uses the filtered data only.
14 Replies
parry2k , thank you for the quick response. Unfortunately, my actual dataset it too large to upload, so hopefully the two examples below will help clarify my issue.
Option Community Plan/Elev Current Cost Min Cost Variance $ Variance % OPTION1 Community1 +/+ 1499 0 1499 100% OPTION1 Community2 +/+ 2173 0 2173 100% OPTION1 Community3 +/+ 1050 0 1050 100% OPTION1 Community4 +/+ 0 0 0 0% OPTION1 Community5 +/+ 1007 0 1007 100% OPTION1 Community6 +/+ 1007 0 1007 100% Community4 deselected in the Comminty slicer, but the minimum of current cost is still 0, instead of 1007
Option Community Plan/Elev Current Cost Min Cost Variance $ Variance % OPTION1 Community1 +/+ 1499 0 1499 100% OPTION1 Community2 +/+ 2173 0 2173 100% OPTION1 Community3 +/+ 1050 0 1050 100% OPTION1 Community5 +/+ 1007 0 1007 100% OPTION1 Community6 +/+ 1007 0 1007 100% Thank you again,
I am just trying to find the lowest/minimum cost for a given option within the group, where the group are the columns in the SUMMARIZE portion of the MINX measure (see columns below).
Options[Option],Community[_cCommunity with Number],Options[_cOption Plan/Elev]
I apologize, I just re-read my earlier post and it was not clear. I re-worded it below.
With Community4 deselected in the Comminty slicer, The desired minimum cost would be 1007, but it remains 0.
Option Community Plan/Elev Current Cost Min Cost Variance $ Variance % OPTION1 Community1 +/+ 1499 0 1499 100% OPTION1 Community2 +/+ 2173 0 2173 100% OPTION1 Community3 +/+ 1050 0 1050 100% OPTION1 Community5 +/+ 1007 0 1007 100% OPTION1 Community6 +/+ 1007 0 1007 100%
Thank you for the suggestion, but unfortunately, this does not take into account the grouping requirement. The suggested measure calculates the minimum cost for all selected options, as opposed to the minimun cost within the group. The measure below works correctly, until a community is deselected in the community slicer.
Cost MIN =CALCULATE(MINX (SUMMARIZE(Options,Options[Option],Community[_cCommunity with Number],Options[_cOption Plan/Elev]),'Option Cost'[Cost Current] ),ALLEXCEPT ( Options, Options[Option], Community[Division]))
Hopefully this will help to clarify the requirement to find the minimum cost within a group, where the group in this case is Option. I have added data examples for 2 options, as opposed to only 1.
Data prior to any slicer filtering. This data is correct, before removing COMMUNITY4.
Option Community Plan/Elev Current Cost Min Option Cost OPTION1 COMMUNITY1 1/A 1007 0 OPTION1 COMMUNITY2 1/A 2173 0 OPTION1 COMMUNITY3 1/A 1007 0 OPTION1 COMMUNITY4 1/A 0 0 OPTION1 COMMUNITY5 1/A 1499 0 OPTION1 COMMUNITY6 1/A 1050 0 OPTION2 COMMUNITY1 1/A 1571 0 OPTION2 COMMUNITY3 1/A 1571 0 OPTION2 COMMUNITY4 1/A 0 0 OPTION2 COMMUNITY6 1/A 1627 0 Data after slicer filtering removing COMMUNITY4, 0 remains the minimum, even though COMMUNITY4 with the minimum of 0 for both OPTION1 and OPTION2, have been removed using slicer selections. There is no longer a Current Cost of 0.
Option Community Number Plan/Elev Current Cost Min Option Cost OPTION1 COMMUNITY1 1/A 1007 0 OPTION1 COMMUNITY2 1/A 2173 0 OPTION1 COMMUNITY3 1/A 1007 0 OPTION1 COMMUNITY5 1/A 1499 0 OPTION1 COMMUNITY6 1/A 1050 0 OPTION2 COMMUNITY1 1/A 1571 0 OPTION2 COMMUNITY3 1/A 1571 0 OPTION2 COMMUNITY6 1/A 1627 0 These are the desired results, after the slicer filtering removing COMMUNITY4.
Option Community Number Plan/Elev Current Cost Min Option Cost OPTION1 COMMUNITY1 1/A 1007 1007 OPTION1 COMMUNITY2 1/A 2173 1007 OPTION1 COMMUNITY3 1/A 1007 1007 OPTION1 COMMUNITY5 1/A 1499 1007 OPTION1 COMMUNITY6 1/A 1050 1007 OPTION2 COMMUNITY1 1/A 1571 1571 OPTION2 COMMUNITY3 1/A 1571 1571 OPTION2 COMMUNITY6 1/A 1627 1571 Thanks again,
Hello, I am still trying to resolve this issue. I have used previous suggestions in the post, which have gotten me closer, but I still need help getting the MINX to work with slicer selections. Below is the link to the desktop file for the data and report examples below.
https://drive.google.com/file/d/10PEccWeAmykc42N9yB5o_OL30ZvkmL4q/view?usp=drive_link
Here are the Curr Cost and Min Curr Cost measures:
_mOption Cost Current Date =
VAR
ActiveCost =
CALCULATE(SUM(OptionCost[Cost]),
FILTER('OptionCost',
('OptionCost'[Cost Type] = "B" &&
'OptionCost'[Effective] <= 'OptionCost'[_cCurrent Date] &&
'OptionCost'[Expiration] > 'OptionCost'[_cCurrent Date])
||
('OptionCost'[Cost Type] = "T" &&
'OptionCost'[Effective] <= 'OptionCost'[_cCurrent Date] &&
'OptionCost'[Expiration] > 'OptionCost'[_cCurrent Date] &&
'OptionCost'[Vendor Assignment Effective] <= 'OptionCost'[_cCurrent Date] &&
'OptionCost'[Vendor Assignment Expiration] > 'OptionCost'[_cCurrent Date])
))
VAR
InactiveCostNoVendAssn =
CALCULATE(SUM('OptionCost'[Cost]),
FILTER('OptionCost',
'OptionCost'[Cost Type] = "T" &&
'OptionCost'[Effective] <= 'OptionCost'[_cCurrent Date] &&
'OptionCost'[Expiration] > 'OptionCost'[_cCurrent Date] &&
'OptionCost'[Vendor Assignment Expiration] < 'OptionCost'[_cCurrent Date]
))
VAR
Cost =
IF(ActiveCost <> BLANK(), ActiveCost, InactiveCostNoVendAssn)
RETURN
Cost
_mOption Cost Current Date MIN =
CALCULATE(
MINX (
SUMMARIZE(
Option,
Option[Option],
Community[Community],
Option[Plan/Elev]),
OptionCost[_mOption Cost Current Date] ),
ALLEXCEPT ( Option, Option[Option], Community[Division]))
On this screen shot, the data is correct.
On the following screenshot, the Min Curr Cost for the Option is not recalculated after deselecting certain communities.
Thank you in advance,
Updated Google Drive link:
https://drive.google.com/file/d/10PEccWeAmykc42N9yB5o_OL30ZvkmL4q/view?usp=sharing
parry2k, thanks again for your response. It is almost there. I wanted to validate it still worked for all communities, and you will notice the minimum for OPTION3 is no longer correct using the new measure. The old measure works for all communities, but not when slicer selections are made to remove communities. The new measure works when slicer selections are made to remove communities, but not when all communities are inclduded.
