Forum Discussion

改善's avatar
改善
New Member
2 years ago

Dynamic column in dashboard that would recalculate based on parameter value?

A very similar question to this one, but the answer in that thread was not working for me. https://community.fabric.microsoft.com/t5/Power-Query/What-if-Parameter-for-Only-Selected-Values/td-p/71664...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, 改善 

Thank you very much for your reply. Leaving the other two columns untouched and not filtered by other charts, I created the following DAX expression:

Sum of Cost 2023 =
CALCULATE ( SUM ( 'Table'[Cost 2023] ), ALL ( 'Table 2'[Required Input] ) )

Sum of cost 2024 =
CALCULATE ( SUM ( 'Table'[Cost 2024] ), ALL ( 'Table 2'[Required Input] ) )

Use the ALL function to clear the external filter. I rewrote the measure Scenario 2024:

Scenario 2024 =
IF (
    ISBLANK ( SELECTEDVALUE ( 'Table 2'[Required Input] ) ),
    CALCULATE (
        MAX ( 'Table'[Cost 2024] ) * ( 1 + [Parameter Value] ),
        FILTER ( 'Table', 'Table'[Required Input] = "Sensor" )
    ),
    IF (
        [Sum of Cost 2023] = BLANK ()
            || [Sum of cost 2024] = BLANK (),
        " ",
        CALCULATE ( MAX ( 'Table'[Cost 2024] ) * ( 1 + [Parameter Value] ) )
    )
)

If no other charts are selected by default, one of the inputs is calculated. When the corresponding input is selected in another chart, Scenario 2024 will also be recalculated. The results are shown in the image below:

If it is not checked, there will be a default output:

When one of the inputs is selected, it is recalculated:

 

I've provided the PBIX file used this time below. 

 

 

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

改善's avatar
改善
New Member
2 years ago

Hi Jianpeng,

 

Thank you for the solution, this is somewhat in the direction i need it to be, i have 2 follow up issues/querries please

 

1) Strictly relating to the model you shared with me above, in the case of Scenario 2024, that column should provide the same cost as in the column cost 2024 if that required input is not selected.

 

i.e. that how its behaving now, but plastic casting should also show 8, processor show 18, sensor 5. The logic should be as follows :

 

IF category is selected,

     then for selected required input multiply COST 2024 * Parameter Value,

else for all other required inputs equals cost 2024.

 

 

 

Also when 2 or more are selected it stops working. 

 

 

 

2) Would it be easier to do this differently? i.e. have 6 parameters a user could change as needed. 

Those 6 parameters would change the values in this table.

 

And then connect this table to the main data through column A primary key, into a new column/measure that would do the cost 2024 multiplied by the parameter value assigned to the required input. 

 

 

 

 

Thank you in advance, i know that my question is not quite simple.