topic Re: Slicer based on Measure doesn't work at higher granularity in DAX Commands and Tips

Slicer based on Measure doesn't work at higher granularity

gspollock — Wed, 17 May 2023 01:18:18 GMT

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?

Re: Slicer based on Measure doesn't work at higher granularity

Wilson_ — Wed, 17 May 2023 01:26:40 GMT

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. 🙂

Re: Slicer based on Measure doesn't work at higher granularity

gspollock — Wed, 17 May 2023 15:41:47 GMT

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

ACCT_MGR_E_ID	Quarter	Number of Customer MBA Shares	MBA Quarterly Goal Met
eh5nea5	1st	4	Yes
ewfboy8	1st	4	Yes
ez2bffd	1st	4	Yes

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

ACCT_MGR_E_ID	Account Number	Share Date	MBA Access Date	MBA Account Value	Meets Quarterly Share Goal	Quarter
ez2bffd	54417757	5/12/2023 20:00		$218,859	No	1st
ez2bffd	54419812	5/12/2023 19:46		$521,172	No	1st
ez2bffd	54424349	4/3/2023 19:27	4/3/2023 19:28	$3,359	No	1st
ez2bffd	56421523	5/12/2023 19:53		$465,198	No	1st
ewfboy8	4239608	5/5/2023 1:19		$1,140,312	No	1st
ewfboy8	54424413	5/5/2023 1:28	5/5/2023 15:08	$34,698	No	1st
ewfboy8	54414832	5/3/2023 1:49		$139,264	No	1st
ewfboy8	58906575	5/3/2023 15:12		$58,841	No	1st
eh5nea5	56420206	5/2/2023 18:02		$165,244	No	1st
eh5nea5	54411588	4/10/2023 13:58	4/10/2023 14:04	$623,791	No	1st
eh5nea5	57783366	5/15/2023 14:02	5/15/2023 14:04	$45,245	No	1st
eh5nea5	82474689	4/10/2023 18:43		$8,075	No	1st