Forum Discussion

skrempp's avatar
skrempp
Frequent Visitor
9 years ago
Solved

Using slicer in measure returns no data

I am having an issue trying to use a value from a slicer as a filter for a measure.   I have a slicer that displays YearQtr.  When the user selects a YearQtr I want to use the corresponding NumQtr ...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi skrempp,

     

    Since you add Opportunity[YearQtr] into slicer and sum Opportunity[Weight] based on slicer, when you select any value from slicer, for example, YearQtr=Quarter1, it will automatically filter table 'Opportunity', so, in current context, the 'Opportunity' table only contains records where YearQtr=Quarter1, in other words, NumQtrs=1. In that case, when you use [SelectedQuarter]+1(NumQtrs=2), it will return blank.

     

    To resolve this problem, you need to create an extra table like below, add [YearQtr] from this table to slicer.

    Quarter Table =
ADDCOLUMNS (
    VALUES ( Opportunity[YearQtr] ),
    "QuarterNum", LOOKUPVALUE ( Opportunity[NumQtr], Opportunity[YearQtr], [YearQtr] )
)

     

    Then, make a little modification to your measure formula.

    SelectedQuarter = If (ISFILTERED('Quarter Table'[YearQtr]), MIN('Quarter Table'[QuarterNum]), 0) 

QtrWeight = CALCULATE(SUM(Opportunity[Weight]),FILTER(Opportunity,Opportunity[NumQtr] = [SelectedQuarter]+1))

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi skrempp,

 

Since you add Opportunity[YearQtr] into slicer and sum Opportunity[Weight] based on slicer, when you select any value from slicer, for example, YearQtr=Quarter1, it will automatically filter table 'Opportunity', so, in current context, the 'Opportunity' table only contains records where YearQtr=Quarter1, in other words, NumQtrs=1. In that case, when you use [SelectedQuarter]+1(NumQtrs=2), it will return blank.

 

To resolve this problem, you need to create an extra table like below, add [YearQtr] from this table to slicer.

Quarter Table =
ADDCOLUMNS (
    VALUES ( Opportunity[YearQtr] ),
    "QuarterNum", LOOKUPVALUE ( Opportunity[NumQtr], Opportunity[YearQtr], [YearQtr] )
)

 

Then, make a little modification to your measure formula.

SelectedQuarter = If (ISFILTERED('Quarter Table'[YearQtr]), MIN('Quarter Table'[QuarterNum]), 0) 

QtrWeight = CALCULATE(SUM(Opportunity[Weight]),FILTER(Opportunity,Opportunity[NumQtr] = [SelectedQuarter]+1))

Best regards,
Yuliana Gu

skrempp's avatar
skrempp
Frequent Visitor
9 years ago

Thank you v-yulgu-msft!

 

This did it.  I had something similar to this several times and it did not work.  I must have had a reference wrong, but it worked this morning.