Forum Discussion
Helper ICalculate not showing total distinct count
vani wrote:Anonymous I have 6 types of football . Your solution won´t work
It is workable if you arrange your types as 1, 2, 4, 8, 16, 32... but... ya, that's pretty geeky :) Ya... should have seen in your huge FILTER() statement about the 6 types.
Okay, let's talk about why your solution isn't working.
Because you have Clubs on rows, each of those rows is evaluating your expression for just that 1 club. And that is doing what you expect (there is a 2, but no other numbers). [though, it is probably easier to do... a HASONEVALUE('Inscrições'[ID TIPO FUTEBOL]) and VALUES('Inscrições'[ID TIPO FUTEBOL]) = 2) ... maybe filtered for positive types... rather than the big condition you hae now].
Anyway, when you get to the grand total... you are NOT looking at just 1 club, you are looking at all clubs... and if you look at the entire set, it just doesn't match your criteria.
I suspect this is an easy way to resolve your issue for the grand total:
FixedCount := SUMX( VALUES ('Inscrições'[ID CLUB] ); [Disctinct Clubs])
Community Championhi vani
More simple is not this:
Disctinct Clubs := calculate (DISTINCTCOUNT('Inscrições'[ID CLUBE UNICO]);filter('Inscrições';
'Inscrições'[ID TIPO FUTEBOL]=2)
Helper IHello Vvelarde ... If i have only have condition 'Inscrições'[ID TIPO FUTEBOL]=2, then , i´m just filtering the clubs that have that type of soccer and not the clubs that only have that type of soccer, because the clubs could have more than on type of soccer.
Example
[ID CLUB];[ID TIPO FUTEBOL]
245;-1
245;-2
245;2
345;-1
345;-2
345;1
445;-1
445;-2
445;1
445;2
RED - Just indoor soccer type club ( because have only the type 2, the other 2 are always present )
GREEN - Just soccer type club
BLUE - both soccer types club
I think this is a situation where the "shape" of your data is kind of hurting you. You would like columns "Has Indoor" and "Has Outdoor" on a table that had just 1 unique row per club.
Anyway, here is my crazy idea:
Club Types := CALCULATE( SUM('Inscrições'[ID CLUB]; FILTER (ALL('Inscrições'); 'Inscrições'[ID TIPO FUTEBOL] > 0) Indoor Only = VAR MyClub = 'Inscrições'[ID CLUB] RETURN IF ( CALCULATE ( [Club Types]; 'Inscrições'[ID CLUB] = MyClub) = 1; "Indoor Only"; CALCULATE ( [Club Types]; 'Inscrições'[ID CLUB] = MyClub) = 2; "Outdoor Only"; "Both"A measure that adds the types together and uses that sum (per Club) to determine the types available. This will only work for... certain allocations of "type" though -- if you have a type 3... this won't work.
- vani
Anonymous I have 6 types of football . Your solution won´t work
vani wrote:Anonymous I have 6 types of football . Your solution won´t work
It is workable if you arrange your types as 1, 2, 4, 8, 16, 32... but... ya, that's pretty geeky :) Ya... should have seen in your huge FILTER() statement about the 6 types.
Okay, let's talk about why your solution isn't working.
Because you have Clubs on rows, each of those rows is evaluating your expression for just that 1 club. And that is doing what you expect (there is a 2, but no other numbers). [though, it is probably easier to do... a HASONEVALUE('Inscrições'[ID TIPO FUTEBOL]) and VALUES('Inscrições'[ID TIPO FUTEBOL]) = 2) ... maybe filtered for positive types... rather than the big condition you hae now].
Anyway, when you get to the grand total... you are NOT looking at just 1 club, you are looking at all clubs... and if you look at the entire set, it just doesn't match your criteria.
I suspect this is an easy way to resolve your issue for the grand total:
FixedCount := SUMX( VALUES ('Inscrições'[ID CLUB] ); [Disctinct Clubs])