Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Using slicer selection in visual with disabled interaction

(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.

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • ChandrashekaraD's avatar
    ChandrashekaraD
    Frequent Visitor
    2 years ago

    Can you please try this

    Calucate(
SUMX(FILTER('DataTable','DataTable'[PeriodKey] = [PreviousPeriodKey]),'DataTable'[Value]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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