cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## ALLEXCEPT behaviour in one to many relationship

Hi All,

I have two tables: A and B. A is a fact table with columns: PlayerId, GameID, Category. B is with columns: Game_id, Game_Name.

A and B have one to many relationship.

What i need is total players by game regardless of the category.

I am really struggling making this working:

CALCULATE ( DistinctCount(TableA[PlayerId]) , ALLEXCEPT ( TableB, TableB[Game_Name] ) )

The result of this is total pLayers by game and category.

Is there a way to make this measure working the way i need?

1 ACCEPTED SOLUTION
Frequent Visitor

Hi guys,

I found where my mistake is. I have Category sorted by Category_Id and turned out that ALL and ALLEXCEPT doesn't work as expected in this case. In order to make it working both Category and Category_Id should be included in ALL clause.

The correct formula for me is:

``````calculate(
distinctcount( TableA[player_id] ),
ALL(TableA[Category_ID]),
ALL(TableA[Category])
)``````

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/

Thank you all for your help.

10 REPLIES 10
Frequent Visitor

Hi guys,

I found where my mistake is. I have Category sorted by Category_Id and turned out that ALL and ALLEXCEPT doesn't work as expected in this case. In order to make it working both Category and Category_Id should be included in ALL clause.

The correct formula for me is:

``````calculate(
distinctcount( TableA[player_id] ),
ALL(TableA[Category_ID]),
ALL(TableA[Category])
)``````

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/

Thank you all for your help.

Anonymous
Not applicable
You don't have to split the conditions into 2. You can use both columns in one ALL: ALL( T[Col1], T[Col2] )
Anonymous
Not applicable

@ivanmezev  Hey Mate ,
you can use Below formula.
Measure = calculate(countrows(table)).
this will automaticly do the context transition .

do you remove all other fiter and keep only selected filter ?

Anonymous
Not applicable

``DistinctCount( TableA[PlayerId] )``

It's enough to drop your game names onto a table/matrix and drop this measure onto it as well. You'll get the number of players for each game regardless of the category. If you drop categories onto the visual, you'll get the number of players by game and category.

If you want a measure that will always disregard the categories, then you should use this measure:

``````calculate(
distinctcount( TableA[PlayerID] ),
removefilters( TableA[Category] )
)``````

Frequent Visitor

hi @Anonymous,

Unfortunately i can't use removefilters when the model is in Analysis.

Anonymous
Not applicable

REMOVEFILTERS in PBI is totally equivalent to ALL, so use this:

``````calculate(
distinctcount( TableA[PlayerID] ),
all( TableA[Category] )
)``````

This formula just calculates the count of distinct PlayerID's after taking off all the filters from Category. Is this not what you wanted?

Community Champion

Hi @ivanmezev  - I admittedly don't know anything about Analysis Services, but can this work?

``````PlayersAllGames =
CALCULATE (
DISTINCTCOUNT ( Player[PlayerID] ),
ALL ( Player[Category] ),
ALL ( Game ),
KEEPFILTERS ( Game[GameID] )
)
``````

Hope this helps

David

Anonymous
Not applicable
removefilters( T[Col] ) = all( T[Col] )
Community Champion

Hi @ivanmezev -

How are you visualizing the output (card, matrix, etc)? Do you have slicers for category, game? Are there any other tables in your model that could be holding onto a filter?

If you can provide some sample data as well it would make it easier to answer your question.

David

Frequent Visitor

My model is in Analysis Services. Dim_Games is connected with the Fact table through game_id, the relationship is 1 to many.

Everything is coming from the fact except game_name. What i am trying to achieve is to calculate % of Total  by category for a certain game. For the purpose i use this formula:

``Distinct Players:= DISTINCTCOUNT(fact[player_id])``
``Players All:= CALCULATE([Distinct Players], ALLEXCEPT(dim_games, dim_games[game_name]))``
``Result:= [Distinct Players]/[Players All]``

I just can't make the second part of the expression working. ALLEXCEPT returns different number for each category where it should be the same.

The output is visualized in a Matrix: Game_Name, Category, % of Total.

The only slicer is for game_name.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors