Forum Discussion

gspollock's avatar
gspollock
Icon for Helper I rankHelper I
3 years ago

Slicer based on Measure doesn't work at higher granularity

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?
 
 
measure
Slicer

2 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi gspollock,

     

    It'd be a lot easier for me to follow along if you provided any data at all to help illustrate your points. 🙂

  • gspollock's avatar
    gspollock
    Icon for Helper I rankHelper I
    3 years ago

    These are three account managers who have at least four shares:

    ACCT_MGR_E_IDQuarterNumber of Customer MBA SharesMBA Quarterly Goal Met
    eh5nea51st4Yes
    ewfboy81st4Yes
    ez2bffd1st4Yes

     

    These are the same account managers with their data at the Account Level:

    ACCT_MGR_E_IDAccount NumberShare DateMBA Access DateMBA Account ValueMeets Quarterly Share GoalQuarter
    ez2bffd544177575/12/2023 20:00 $218,859No1st
    ez2bffd544198125/12/2023 19:46 $521,172No1st
    ez2bffd544243494/3/2023 19:274/3/2023 19:28$3,359No1st
    ez2bffd564215235/12/2023 19:53 $465,198No1st
    ewfboy842396085/5/2023 1:19 $1,140,312No1st
    ewfboy8544244135/5/2023 1:285/5/2023 15:08$34,698No1st
    ewfboy8544148325/3/2023 1:49 $139,264No1st
    ewfboy8589065755/3/2023 15:12 $58,841No1st
    eh5nea5564202065/2/2023 18:02 $165,244No1st
    eh5nea5544115884/10/2023 13:584/10/2023 14:04$623,791No1st
    eh5nea5577833665/15/2023 14:025/15/2023 14:04$45,245No1st
    eh5nea5824746894/10/2023 18:43 $8,075No1st