Forum Discussion
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 as a filter in a measure.
SelectedQuarter = If (ISFILTERED(Opportunity[YearQtr]), MIN(Opportunity[NumQtrs]), 0)
This formula returns a single value that I would like to use in a measure to as part of a calculation. I have put SelectedQuarter in a card visual to make sure the value I'm expecting is returned.
I then created a measure to use this SelectedQuarter slicer. When I do this there are no results, just blank. I can change the reference to [SelectedQuarter] in the measure to a hard coded number and the data appears. I tried this multiple ways, two examples are below.
QtrWeight = CALCULATE(SUM(Opportunity[Weight]),FILTER(Opportunity,Opportunity[NumQtrs] = [SelectedQuarter]+1))
QtrWeight = CALCULATE(SUM(Opportunity[Weight]),FILTER(Opportunity,Opportunity[NumQtrs] = IF(ISFILTERED(Opportunity[YearQtr]),Opportunity[NumQtrs]+1,1)))
I have spent hours trying to figure out where I went wrong. Any help the community can give me will be greatly appriciated.
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
4 Replies
Hi skrempp,
From the DAX formula, it seems there is nothing wrong with its syntax. You said the measure returned blank, did it show blank in table or chart visual? What about adding it in a card visual? You said it could return desired result if replacing the measure [SelectedQuarter] with hard coded number, right? Please check whether the values returned by [SelectedQuarter] are exactly matched with Opportunity[NumQtrs].
Since I was not able to reproduce your issue without any sample data, please share detailed data for test and provide screenshot of current result you have got.
Regards,
Yuliana GuI will pull some sample data and post in the next few hours. I spent several more hours playing with this yesterday and I figured out if I remove the '+1' the formula works. I get the data for the quarter selected. I am displaying the data in a table and some columns display data for the quarter the user selects and then other columns like the one I have posted my DAX for are pulling data for the quarter before the one the user selects or a range of quarters based on the one the user sets.
I guess my questions now is why would the +1 cause this query to fail? I know the value returned by [SelectedQuarter] is a number. I have a card visual displaying [SelectedQuarter] + 1 to test to make sure it is returning the right value and it is correct. I have also used VALUE([SelectedQuarter])+1 in my formulat to make sure that [SelectedQuarter] is returning as a number and not text. I verified Opportunity[NumQtrs] is an int.
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
