topic Re: Using slicer selection in visual with disabled interaction in DAX Commands and Tips

Using slicer selection in visual with disabled interaction

Anonymous — Mon, 02 Oct 2023 09:11:45 GMT

(updated)
Hi, community

I am trying to calculate some values for selected and previous custom time periods. I have indexed the time periods to be able to match them and managed to identify a previous period with a formula:

PreviousPeriodKey = SELECTEDVALUE('PeriodLookup'[PeriodKey])-1

When I show this measure on the page with active slicer selection it shows correct value.
However, when I try to use it in a calculation, regardless whether I save it as a separate measure, or use in VAR, I always get blank result on the very same page.

Here is the measure I am trying to use:

Measure = SUMX(FILTER('DataTable','DataTable'[PeriodKey] = [PreviousPeriodKey]),'DataTable'[Value])

Adding calculate at the beginning as suggested below does not help. The issue is that when I keep the slicer filter active over the visual, the PreviousPeriodKey works well but the measure cannot retrieve data earlier than selected PeriodKey. And when the interaction between slicer and visual is disabled, the SELECTEDVALUE can't work properly.
The idea is to be able to calculate different measures for two consecutive category indexes (PeriodKey in the sample).
I have already checked the format of different columns and the measure itself as well as proper relationships are established between the tables.

Re: Using slicer selection in visual with disabled interaction

ChandrashekaraD — Fri, 29 Sep 2023 16:02:48 GMT

Can you please try this

Calucate( SUMX(FILTER('DataTable','DataTable'[PeriodKey] = [PreviousPeriodKey]),'DataTable'[Value]))

Re: Using slicer selection in visual with disabled interaction

Anonymous — Mon, 02 Oct 2023 07:30:49 GMT

Unfortunately, this does not help... I wonder if this might be connected to the fact that I am trying to use a value (PreviousPeriodKey) that is based on slicer selection but in the calculation I am using formulas that might override it?

Re: Using slicer selection in visual with disabled interaction

Anonymous — Thu, 05 Oct 2023 07:13:21 GMT

Hi Anonymous ,

You can update the formula of measure as below and check if it can return the correct result...

Measure = VAR prepkey = SELECTEDVALUE ( 'PeriodLookup'[PeriodKey] ) - 1 RETURN SUMX ( FILTER ( ALLSELECTED ( 'DataTable' ), 'DataTable'[PeriodKey] = prepkey ), 'DataTable'[Value] )

If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards