What-If Parameter Usage in Measure Failing in Table Visual

[Dailem]

I have an existing PBI report which has a single fact table & several dimension tables which works as expected.  Recently, I had a request to introduce the ability to add a column to the report table visual which represented a measure tied back to a value the user could change on-demand.  After researching, I decided to introduce a what-if parameter (including slicer) which has a numeric range & returns a single value.  For reference, the new parameter table created was entitled [p_Fee_Amount] & it is a disconnected table from the rest of the symantic model.

When I create a card visual & use the related measure created by the what-if parameter wizard as the value for the card, it returns the selected value of the slicer as anticipated.  However, if I attempt to add this same measure to the existing report table visual, it will eventually fail.  Even though the parameter table is disconnected, I would expect that a measure which is returning the SELECTEDVALUE for the slicer & which works correctly in a card would fail?  My confusion is partly due to the fact that if I add a new measure referencing the same parameter value slightly differently, it works.  Here is that code:

     Test1 = CALCULATE(SUM(Fact_Perf_ROR[1Year_SvcLine_Return])) * (1 + p_Fee_Amount[p_Fee_Amount Value])

...this DAX measure works in the table visual (even the p_Fee_Amount[p_Fee_Amount Value] measure by itself did not) & is closer to the measure I'm attempting to compute.  When I change the measure above to the exact calculation I want (which is the 1Year_SvcLine_Return minus the p_Fee_Amount Value tied to the single-value parameter slicer), it fails.  Here is that measure:

     GoalMeasure = CALCULATE(SUM(Fact_Perf_ROR[1Year_SvcLine_Return])) - p_Fee_Amount[p_Fee_Amount Value

I have tried countless different variations of the GoalMeasure above, but I cannot get anything to work.  For some reason the Test1 measure successfully manages the granularity issues associated with the parameter value, but attempting to subtract the fee value or referencing it directly within the table visual eventually causes a failure.  

I'm hoping someone can provide some insight as to the correct approach for this or if it is possible.  Ran the scenario through multiple AI tools as well, but didn't get me to a solution.  Glad to provide more info if needed...Thanks.

[SagarChauhan1]

Hi, I have a table where i have 3 columns and i need slicer with some measure where i can get total of 3 acconding to slicer selection.

[vaibhavmahajan]

Hi @Dailem,

I hope you are doing well today 🙂❤️

You can resolve this issue by forcing the What-If parameter to always return a scalar value

Try the following approach:

Fee Amount :=
MAX ( 'p_Fee_Amount'[p_Fee_Amount] )

GoalMeasure :=
VAR Fee = [Fee Amount]
RETURN
CALCULATE (
SUM ( Fact_Perf_ROR[1Year_SvcLine_Return] )) - Fee

If this answer helped, kindly give Kudos and mark it as the Accepted Solution

to help other members find it more quickly.

Best regards,

Vaibhav Mahajan

LinkedIn: https://www.linkedin.com/in/vaibhavnmahajan

[v-sshirivolu]

Hi @Dailem ,

I would also take a moment to thank @Ahmed-Elfeel, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[v-sshirivolu]

Hi @Dailem ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[GeraldGEmerick]

@Dailem Example data and all of your measure formulas would be extremely helpful here in order to try to sort out what is going on. For example, I don't know what the formula for the p_Fee_Amount Value measure is exactly so hard to tell why it does not work. Also, while there might be a reason you are using CALCULATE here, you aren't using any kind of filter clause with it so it may not be necessary at all. 

Also, one other thing to consider, the SELECTEDVALUE function will return BLANK by default if more than one value is selected for whatever reason which can cause unwanted behavior. Sometimes using a simple MAX in place of SELECTEDVALUE can solve these kinds of issues or using the optional parameter for SELECTEDVALUE to return a default value.

[Ahmed-Elfeel · Accepted solution]

Hi @Dailem,

I hope you are doing well today☺️❤️

This is Look Like a context transition issue with the disconnected parameter table in a table visual

• The problem is related to how DAX evaluates measures row by row in table visuals..

So you can try These Approaches:

First Approach: Use SUMX with appropriate context

GoalMeasure = 
SUMX(
    VALUES(Fact_Perf_ROR[YourKeyColumn]),  // Or use your table directly
    CALCULATE(SUM(Fact_Perf_ROR[1Year_SvcLine_Return])) - 
    CALCULATE(MAX(p_Fee_Amount[p_Fee_Amount Value]))
)

Second Approach: Force evaluation of the parameter at the right level

GoalMeasure = 
VAR TotalReturn = CALCULATE(SUM(Fact_Perf_ROR[1Year_SvcLine_Return]))
VAR FeeValue = 
    CALCULATE(
        SELECTEDVALUE(p_Fee_Amount[p_Fee_Amount], 0),  // Use the column not measure
        ALLSELECTED(p_Fee_Amount)
    )
RETURN
TotalReturn - FeeValue

Third Approach: Use an aggregator function (I Recommend it)

GoalMeasure = 
CALCULATE(SUM(Fact_Perf_ROR[1Year_SvcLine_Return])) - 
MAX(p_Fee_Amount[p_Fee_Amount])  // Use MAX or MIN instead of the measure

Bonus Approach: Use SELECTEDVALUE() explicitly (This will work perfectly also)

GoalMeasure =
VAR TotalReturn =
    CALCULATE(SUM(Fact_Perf_ROR[1Year_SvcLine_Return]))
VAR FeeValue =
    SELECTEDVALUE(p_Fee_Amount[p_Fee_Amount Value], 0)
RETURN
TotalReturn - FeeValue

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.