Forum Discussion
Using slicer in measure returns no data
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
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 Gu
I 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 GuThank 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.