MINX Measure Ignoring Slicer Filtering

curtismob · ‎08-19-2023

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.

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]))

Cost Current =

VAR

ActiveCost =

CALCULATE(SUM('Option Cost'[Cost]),

FILTER('Option Cost',

('Option Cost'[Bid/Takeoff] = "B" &&

'Option Cost'[Effective] <= 'Option Cost'[_cCurrent Date] &&

'Option Cost'[Expiration] > 'Option Cost'[_cCurrent Date])

||

('Option Cost'[Bid/Takeoff] = "T" &&

'Option Cost'[Effective] <= 'Option Cost'[_cCurrent Date] &&

'Option Cost'[Expiration] > 'Option Cost'[_cCurrent Date] &&

'Option Cost'[Vendor Assignment Effective] <= 'Option Cost'[_cCurrent Date] &&

'Option Cost'[Vendor Assignment Expiration] > 'Option Cost'[_cCurrent Date]) &&

'Option Cost'[Vendor Number] = 'Option Cost'[_mFirst Takeoff Vendor]

))

VAR

InactiveCostNoVendAssn =

CALCULATE(SUM('Option Cost'[Cost]),

FILTER('Option Cost',

'Option Cost'[Bid/Takeoff] = "T" &&

'Option Cost'[Effective] <= 'Option Cost'[_cCurrent Date] &&

'Option Cost'[Expiration] > 'Option Cost'[_cCurrent Date] &&

'Option Cost'[Vendor Assignment Expiration] < 'Option Cost'[_cCurrent Date]

// && 'Option Cost'[Vendor Number] = 'Option Cost'[_mFirst Takeoff Vendor]

))

VAR

Cost =

IF(ActiveCost <> BLANK(), ActiveCost, InactiveCostNoVendAssn)

RETURN

Cost + 'Option Cost'[_mMin Zero]

Thank you in advance,

@curtismob

parry2k · ‎09-05-2023

@curtismob try this:

MINX ( ALLSELECTED ( Community[Community] ), [_mOption Cost Current Date] )

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

curtismob · ‎09-07-2023

@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.

curtismob · ‎08-30-2023

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,

@curtismob

curtismob · ‎09-05-2023

Updated Google Drive link:

https://drive.google.com/file/d/10PEccWeAmykc42N9yB5o_OL30ZvkmL4q/view?usp=sharing

parry2k · ‎08-21-2023

@curtismob not sure what that means. What do you mean by grouping? if you clarify everything in one go, it will help.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

curtismob · ‎08-21-2023

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,

@curtismob

parry2k · ‎08-20-2023

@curtismob

Minimum Cost = 
MINX ( ALLSELECTED ( YourTable ), [Current Cost] )

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

curtismob · ‎08-21-2023

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]))

parry2k · ‎08-19-2023

@curtismob given the example of your data what do you expect the minimum to be?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

curtismob · ‎08-20-2023

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%

parry2k · ‎08-19-2023

@curtismob thanks for sharing the sample data but what you are trying to achieve, could you please explain that?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

curtismob · ‎08-19-2023

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]

parry2k · ‎08-19-2023

@curtismob it will be easier if you drop some sample data with the expected output.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

curtismob · ‎08-19-2023

@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,

@curtismob

MINX Measure Ignoring Slicer Filtering