Adjusting the Total Range Based on User-Selected Parameter Values

[mh20221111]

There are two columns and three measures as shown in the figure. When the column 'size' is sorted in descending order, there is always one size for which the result of the formula set for measure 1 is blank, and the result of measure 3's formula at that time is always 0. We will provide a parameter that allows the user to choose any value they like.

For example, if the user inputs 50 as the parameter, I would like to calculate the total values of measure 1 and measure 2 for sizes that are both equal to 0 for measure 3 and less than or equal to 50.

What settings should be applied to the measures to achieve this? Sizes with values for measure 3 less than 0 or greater than the user-input parameter will not be included in the sum.

[v-bmanikante]

Hi @mh20221111,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[mh20221111]

Hi @v-bmanikante ,

Thank you very much. However, I feel that the difference between a column and a measure is not being understood, and it feels somewhat like I'm conversing with Copilot. A measure cannot take the form of a Fully Qualified Reference, meaning that you cannot write 'Table'[Measure3] in the conditions of the Calculate function. Keeping this in mind, I would like a correct response that truly works.

[OwenAuger]

@mh20221111 I'll try to help here (unless someone else does first).

Could I just check the filter condition:

Did you want the filter to select Sizes for which 

1. 0 ≤ [Measure3] ≤ Parameter; or
2. ( [Measure3] = 0 ) AND ( Size ≤ Parameter )

To test an example, assuming a parameter value of 50, which sizes in the table in your original would be included?

1. 0 ≤ [Measure3] ≤ 50: Sizes 14.6, 15.0, 15.4, 15.8, and 16.1
2. ( [Measure3] = 0 ) AND ( Size ≤ 50 ): Size 16.1 only

Regards

[mh20221111]

Hi @OwenAuger ,
The expected behavior is that when the parameter value is 50, the calculations will be based on the values for sizes 14.6, 15.0, 15.4, 15.8, and 16.1( where 0 ≤ [Measure3] ≤ 50).


Regards,
mh20221111

[OwenAuger · Accepted solution]

Thanks @mh20221111 🙂

I'm assuming that

• Parameter Value is a measure returning the parameter value.
• Measure1 and Measure2 need to be evaluated for each Commodity/SIZE then summed (but easily adjusted if you just need to evaluate per SIZE then sum).

This would then be the Sum Measure1 measure:

Sum Measure1 = 
VAR ParameterValue = [Parameter Value]
VAR Result =
    SUMX (
        SUMMARIZE ( 'Table', 'Table'[Commodity], 'Table'[SIZE] ),
        VAR Measure3Value = [Measure3]
        RETURN
            IF (
                AND ( Measure3Value >= 0, Measure3Value <= ParameterValue ),
                [Measure1]
            )
    )
RETURN
    Result

If Measure1 and Measure2 produce the same result whether summed per Commodity/SIZE or evaluated as-is, you could write this:

Sum Measure1 v2 = 
VAR ParameterValue = [Parameter Value]
VAR CommoditySIZE =
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Commodity], 'Table'[SIZE] ),
        VAR Measure3Value = [Measure3]
        RETURN
            AND ( Measure3Value >= 0, Measure3Value <= ParameterValue )
    )
VAR Result =
    CALCULATE (
        [Measure1],
        CommoditySIZE
    )
RETURN
    Result

You can change the SUMMARIZE expression to VALUES ( 'Table'[SIZE] ) if you don't need Commodity to be included.

Does this work for you?

[mh20221111]

Hi @OwenAuger 
Thank you for your guidance. Thanks to your help, it seems that I will be able to implement what I want to do!

Regards,
mh20221111

[BeaBF]

@mh20221111 Hi!

FilteredMeasure1Sum =
VAR SelectedSizeValue = SELECTEDVALUE('ParameterTable'[SelectedSize])
RETURN
CALCULATE(
SUM('Table'[Measure1]),
'Table'[Measure3] = 0,
'Table'[SIZE] <= SelectedSizeValue
)

FilteredMeasure2Sum =
VAR SelectedSizeValue = SELECTEDVALUE('ParameterTable'[SelectedSize])
RETURN
CALCULATE(
SUM('Table'[Measure2]),
'Table'[Measure3] = 0,
'Table'[SIZE] <= SelectedSizeValue
)

If these measures are incorrect, please sgare the measures in table and I'll try to fix.

BBF

[mh20221111]

Thank you. By the way, the measures 1 to 3 are not columns in the table but are measures defined by formulas. Can measures also be used as filter conditions in the CALCULATE function?

mh

[BeaBF]

@mh20221111 Yes, adjusted:

FilteredMeasure1Sum =
VAR SelectedSizeValue = SELECTEDVALUE('ParameterTable'[SelectedSize])
RETURN
CALCULATE(
[Measure1], -- Calculate Measure1 in the filtered context
'Table'[Measure3] = 0, -- Filter condition 1
'Table'[SIZE] <= SelectedSizeValue -- Filter condition 2
)

FilteredMeasure2Sum =
VAR SelectedSizeValue = SELECTEDVALUE('ParameterTable'[SelectedSize])
RETURN
CALCULATE(
[Measure2], -- Calculate Measure2 in the filtered context
'Table'[Measure3] = 0, -- Filter condition 1
'Table'[SIZE] <= SelectedSizeValue -- Filter condition 2
)

BBF