I am using a slicer based on a measure called Meets Quarterly Share Goal which is determined at the Account Manager level. Meets Quarterly Share Goal is a "Yes"/"No" result for each Account Manager. It is based on a Measure Number Accounts Shared which is calculated across all accounts for each Account Manager. If Number Accounts Shared is at least 4 for an Account Manager, then Meets Annual Share Goal = "Yes".
Number Accounts Shared = CALCULATE(DISTINCTCOUNT(V_MBA_MMS_REPORT_SHARE[account_id]))
To be able to use Meets Quarterly Share Goal in a slicer, I defined this table which will produce the "Yes"/"No" in the slicer:
define Table Quarterly Goal Summary = VAR X1 = SUMMARIZECOLUMNS
(
'V_MBA_MMS_REPORT_SHARE'[acct_mgr_name], V_DIM_PERIOD[FISC_YR_QTR_NUM], V_DIM_PERIOD[FISC_YR_NUM],
"Meets Quarterly Goal", V_MBA_MMS_REPORT_SHARE[Meets Quarterly Share Goal]
) RETURN summarize(x1,[Meets Quarterly Goal])
I defined this measure to filter the results on the table where Current Year filter =1:
Current Year filter =
var x1=SELECTEDVALUE('define Table Annual Goal Summary'[Meets Annual Goal])
var maxgoal = maxx(all('V_MBA_MMS_REPORT_SHARE'),[Meets Annual Share Goal])
return
IF(ISBLANK(x1),1, if(maxgoal="Yes", 1,0))
The slicer works correctly on a table showing results at the Account Manager level. However, I have another table at a higher granularity showing each account associated with each Account Manager.
Meets Quarterly Share Goal will always be "No" because there can't be four accounts at a single account level. When "Yes" is selected in the slicer there are zero records in the more detailed table. How can it be set up so that the more detailed table responds correctly to the slicer?
