Calculated column slicer and parameter adjustments dilemma

[1up]

Hi,

I've got beat at this problem at the moment so would appreciate some help,

Trying to

1) allow the user a simple user interface where he / she can select a temperature range say from 5 up to 15 degrees from a slicer, based on a calculated column. And

2) allow the user to set three separate adjustment factors. All factors in combination should affect the resulting table visual so that it still is only viewing the selected temperature range in the slicer.

Example, all factors are set to 2, it means the slicer settings 5-15 is still the same, but all row values should be multiplied by 6 and the end result table still only show rows where the value is within the 5-15 degrees range.

Is this possible, perhaps I need to design it differently?

Only calculated columns can be used as slicers in my understanding. The slicer is bound to the calculated column, but I guess perhaps it is possible to create a measure which is going to show the adjusted temperatures for each row. And then would like the slicer settings from and to to set the measure range shown. Perhaps using a filter and a and-statement with Selected value-function?

Seems a bit like a work-around for something that could be made more simple?

How would you implement the functionality?

[1up · Accepted solution]

Thanks for the reply Ibendlin.

I think I created a measure yesterday that works. Although, so slow that after waiting several minutes without the table visual finishing, it seems the approach is not a usable one.

The measure uses a sumx-statement with the above mentioned slicer values as filters, acting on millions of rows of data. I am thinking perhaps using variables and / or a leading filter in the DAX-statement could speed things up.

Perhaps I need to leave this implementation be.

[1up · Accepted solution]

Thanks for the reply Ibendlin.

I think I created a measure yesterday that works. Although, so slow that after waiting several minutes without the table visual finishing, it seems the approach is not a usable one.

The measure uses a sumx-statement with the above mentioned slicer values as filters, acting on millions of rows of data. I am thinking perhaps using variables and / or a leading filter in the DAX-statement could speed things up.

Perhaps I need to leave this implementation be.

[Anonymous · Accepted solution]

Hi @1up, hello lbendlin, thank you for your prompt reply! 

I agree with your perspective.

It is essential to filter as much data as possible before executing logical tests.

You could use the Performance Analyzer in power bi desktop to check the time reduction before and after: 

Here are two more articles on performance tuning dax for your reference:

Performance Tuning DAX - Part 1 - Microsoft Fabric Community

Performance Tuning DAX - Part 2 - Microsoft Fabric Community

Best regards,

Joyce

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

[lbendlin]

Only calculated columns can be used as slicers in my understanding

Any column can be used, either pre-existing or calculated.

Your measure approach seems reasonable. What have you tried and where are you stuck?