topic DAX measure not working when summarizing? in DAX Commands and Tips

DAX measure not working when summarizing?

Anonymous — Fri, 23 Aug 2024 03:00:43 GMT

I have this DAX measure that takes the value from a filter the user can select (based on a customer list of users) "FilterSelect", then looks through every one of these manager+X fields to see whether the selected person is in any of those fields. If so, it returns a 1, else a 0. This works great on the row level, so I can filter my user's table visual by this value, but it will absolutely not filter the summary visuals (bar chart and summary table). It just makes those visuals blank. Please help, I have spent hours on this.

IsInReportingStructureAAC =

VAR empSelect = [FilterSelect]

RETURN

IF(

SELECTEDVALUE('All Active Controls'[Controls Owner])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+1])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+2])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+3])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+4])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+5])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+6])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+7])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+8])=empSelect ||

SELECTEDVALUE('All Active Controls'[Manager+9])=empSelect,

1, 0

)

Re: DAX measure not working when summarizing?

bhanu_gautam — Fri, 23 Aug 2024 06:38:34 GMT

Anonymous , Try using below DAX

DAX
IsInReportingStructureAAC =
VAR empSelect = [FilterSelect]
RETURN
IF (
COUNTROWS (
FILTER (
'All Active Controls',
'All Active Controls'[Controls Owner] = empSelect ||
'All Active Controls'[Manager] = empSelect ||
'All Active Controls'[Manager+1] = empSelect ||
'All Active Controls'[Manager+2] = empSelect ||
'All Active Controls'[Manager+3] = empSelect ||
'All Active Controls'[Manager+4] = empSelect ||
'All Active Controls'[Manager+5] = empSelect ||
'All Active Controls'[Manager+6] = empSelect ||
'All Active Controls'[Manager+7] = empSelect ||
'All Active Controls'[Manager+8] = empSelect ||
'All Active Controls'[Manager+9] = empSelect
)
) > 0,
1,
0
)

Re: DAX measure not working when summarizing?

Anonymous — Fri, 23 Aug 2024 16:00:57 GMT

This stops the summarized visuals from blanking out, but the counts are all wrong. For example, in the table visual of each individual row, with the current selection in the filter there are 14 items. However, the summary shows a count of 920. I don't know where this number is coming from, particularly becuase there are 2446 total records.

Re: DAX measure not working when summarizing?

sjoerdvn — Mon, 26 Aug 2024 09:51:18 GMT

You haven't shared information on your model, like what table that "row level" is actually based on.
Anyway, I guess it could work with a SUMX function, I will add an example below, but you will have to put in the proper table and column name.

IsInReportingStructureAAC = VAR empSelect = [FilterSelect] RETURN SUMX(VALUES('User'[User ID]), CALCULATE( IF( SELECTEDVALUE('All Active Controls'[Controls Owner])=empSelect || SELECTEDVALUE('All Active Controls'[Manager])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+1])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+2])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+3])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+4])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+5])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+6])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+7])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+8])=empSelect || SELECTEDVALUE('All Active Controls'[Manager+9])=empSelect, 1, 0 ) ))

Re: DAX measure not working when summarizing?

Anonymous — Mon, 26 Aug 2024 10:35:34 GMT

Thanks for the reply from sjoerdvn and bhanu_gautam , please allow me to provide another insight:

Hi, Anonymous

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Second, I've created the following parameter values:

3. Below are the measure I've created for your needs:

Measure = VAR cc1=MAX('Parameter'[Parameter]) VAR cc2=VALUES('Table'[All Active Controls_Controls Owner]) VAR cc3=SWITCH(TRUE(),MAX('Parameter'[Parameter])="Controls Owner", IF(CONTAINS('All Active Controls','All Active Controls'[Controls Owner],cc2),1,0), MAX('Parameter'[Parameter])="Manager", IF(CONTAINS('All Active Controls','All Active Controls'[Manager],cc2),1,0), MAX('Parameter'[Parameter])="Manager+1", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+1],cc2),1,0), MAX('Parameter'[Parameter])="Manager+2", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+2],cc2),1,0), MAX('Parameter'[Parameter])="Manager+3", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+3],cc2),1,0), MAX('Parameter'[Parameter])="Manager+4", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+4],cc2),1,0), MAX('Parameter'[Parameter])="Manager+5", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+5],cc2),1,0), MAX('Parameter'[Parameter])="Manager+6", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+6],cc2),1,0), MAX('Parameter'[Parameter])="Manager+7", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+7],cc2),1,0), MAX('Parameter'[Parameter])="Manager+8", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+8],cc2),1,0), MAX('Parameter'[Parameter])="Manager+9", IF(CONTAINS('All Active Controls','All Active Controls'[Manager+9],cc2),1,0) ) RETURN IF(ISFILTERED('Table'[All Active Controls_Controls Owner]),cc3,1)

4.Then, modify the settings for the slicer's visuals:

5.Select a selection of all slicers:

6.Here's my final result, which I hope meets your requirements:

The downside of this method is that you need to select all slicers in this intermediate step.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.