Multiple Slicer from Different DIM TABLES with Mutiple ALLSELECTED DAX statements

DAX DAX Calculations
1

Is there a way to to write ALLSELECTED DAX with multiple slicer values where slicers come from different Dim Tables?

Example:

Slicer 1 has Project Name (DimProject[ProjectName])

Slicer 2 has Project Manager (DimTeam[ProjectManager])

Slicer 3 has Calendar Year & Month (DimDate[CalendarYearAndMonth])

I tried the following: CACULATE(SUM(COST), ALLSELECTED(DimProject[ProjectName]), ALLSELECTED(DimTeam[ProjectManager]), ALLSELECTED(DimDate[CalendarYearAndMonth]) )

It didn’t work. Can anyone advise on what is the correct way to filter with multiple slicer values [from different tables] while using ALLSELECTED?

2

@bslyew,

Try just using ALLSELECTED(), e.g.,

CALCULATE( [Total Cost], ALLSELECTED() )

  • Brian
2 Likes
3

I’m not sure it worked, but i appreciate the response/help. Why would a person use ALLSELECED() with no arguments listed vs. ALLSELECTED() w/ arguments?

4

@bslyew,

The simplest answer (and when it comes to ALLSELECTED, nothing is simple) is that when you specify columns within ALLSELECTED, they must all come from the same table. ALLSELECTED with no parameters lets you return the shadow filter context across the entire data model.

Here’s the definitive explanation of how ALLSELECTED works, but it’s tough sledding:

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

@AntrikshSharma explains this better than anyone I know. Let me send up the bat signal to him, and see if he can help out here…

  • Brian
1 Like
5

You know, I’ve read this guide multiple times over and over, I thought I understood ALLSELECTED…and then I asked more questions…and then realized I didn’t actually understand ALLSELECTED

6

@bslyew,

You’re in good company. Russo and Ferrari wrote their first ALLSELECTED article (this one) and then wrote the other one because THEY realized they didn’t fully understand it either. For me, the functions that have caused me the most trouble over time are ALLSELECTED at #1 by a wide margin and RANKX at #2, and like you, I’ve read that article and the relevant sections of the Definitive Guide over and over.

  • Brian
1 Like
7

Hi @bslyew, please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

8

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!