ALLEXCEPT behaviour in one to many relationship

[ivanmezev]

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?

[ivanmezev · Accepted solution]

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])
)

 There is a very interesting article about this here:

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

Thank you all for your help.

[Anonymous]

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

[Anonymous]

@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 ?

https://docs.microsoft.com/en-us/dax/allexcept-function-dax 

Cheers

[Anonymous]

This is your measure:

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] )
)

[ivanmezev]

hi @Anonymous,

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

[Anonymous]

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?

[dedelman_clng]

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]

removefilters( T[Col] ) = all( T[Col] )

[dedelman_clng]

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

[ivanmezev]

hi @dedelman_clng,

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.