topic Re: ALLEXCEPT behaviour in one to many relationship in DAX Commands and Tips

ALLEXCEPT behaviour in one to many relationship

ivanmezev — Mon, 19 Oct 2020 11:09:56 GMT

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?

Re: ALLEXCEPT behaviour in one to many relationship

dedelman_clng — Mon, 19 Oct 2020 14:26:19 GMT

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

Re: ALLEXCEPT behaviour in one to many relationship

Anonymous — Mon, 19 Oct 2020 16:07:37 GMT

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

Re: ALLEXCEPT behaviour in one to many relationship

ivanmezev — Mon, 19 Oct 2020 18:31:50 GMT

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.

Re: ALLEXCEPT behaviour in one to many relationship

ivanmezev — Mon, 19 Oct 2020 18:34:13 GMT

hi Anonymous,

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

Re: ALLEXCEPT behaviour in one to many relationship

Anonymous — Mon, 19 Oct 2020 21:05:34 GMT

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

Re: ALLEXCEPT behaviour in one to many relationship

dedelman_clng — Mon, 19 Oct 2020 21:12:11 GMT

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

Re: ALLEXCEPT behaviour in one to many relationship

Anonymous — Tue, 20 Oct 2020 10:48:40 GMT

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

Re: ALLEXCEPT behaviour in one to many relationship

Anonymous — Tue, 20 Oct 2020 11:56:37 GMT

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?

Re: ALLEXCEPT behaviour in one to many relationship

ivanmezev — Tue, 20 Oct 2020 13:49:57 GMT

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.

Re: ALLEXCEPT behaviour in one to many relationship

Anonymous — Tue, 20 Oct 2020 14:06:20 GMT

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