Skip to main content
cancel
Showing results for 
Search instead 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

Reply
vani
Helper I
Helper I

Calculate not showing total distinct count

Hello ,

 

i have the following formula:

 

Disctinct Clubs := calculate (DISTINCTCOUNT('Inscrições'[ID CLUBE UNICO]);filter('Inscrições';
CONTAINS(VALUES('Inscrições');'Inscrições'[ID TIPO FUTEBOL];2) &&
NOT(CONTAINS(VALUES('Inscrições');'Inscrições'[ID TIPO FUTEBOL];1)) &&
NOT(CONTAINS(VALUES('Inscrições');'Inscrições'[ID TIPO FUTEBOL];3)) &&
NOT(CONTAINS(VALUES('Inscrições');'Inscrições'[ID TIPO FUTEBOL];4)) &&
NOT(CONTAINS(VALUES('Inscrições');'Inscrições'[ID TIPO FUTEBOL];5)) &&
NOT(CONTAINS(VALUES('Inscrições');'Inscrições'[ID TIPO FUTEBOL];6)) =TRUE))

 

Showing the result on a power BI card it is showing "BLANK()" , but when i insert a dimension, it is showing values (with no total)

 DISTINCTCLUBS.png

 My question is:

 

Why it is not showing the total on both visualizations ? 

 

Hope you guys can help me 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable


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

 

 

View solution in original post

11 REPLIES 11
jpereztang
Helper I
Helper I

@vani I replicate your example with other info and this is that i got.

 

Why don't you try to achieve it first without filters? And also check if category label in format is on.

 

replicate.png

Hello @jpereztang,

 

My formula is retrieving " right " results, but i don´t know why is not presenting the  sum value of distinct clubs when i put the measure on a (example ) " card " visualization. I would be greatful if anyone has a workaroud. The thing is to know on a column if a club has [ID CLUBE FUTEBOL] = 2  but it cant have [ID CLUBE FUTEBOL] = 1. Because a club could have more than 1 type of soccer ! 

Vvelarde
Community Champion
Community Champion

hi @vani

 

More simple is not this:

 

Disctinct Clubs := calculate (DISTINCTCOUNT('Inscrições'[ID CLUBE UNICO]);filter('Inscrições';
'Inscrições'[ID TIPO FUTEBOL]=2)




Lima - Peru

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

 

 

 

 

 

 

 

Anonymous
Not applicable

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.

 

@Anonymous I have 6 types of football . Your solution won´t work 

Anonymous
Not applicable


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

 

 

@Anonymous many thanks ! For now it resolves my issue. 

 

 

Vvelarde
Community Champion
Community Champion

hi @vani

 

My solution to this was:

 

DistinctClub = CALCULATE(DISTINCTCOUNT('Futebol-Club'[IDCLUB]);FILTER('Futebol-Club';COUNT('Futebol-Club'[IDCLUB])=1);FILTER('Futebol-Club';'Futebol-Club'[ID TIPO FUTBOL]=2))

 

GrandTotal = SUMX( VALUES ('Futebol-Club'[IDCLUB]);[DistinctClub])

 

Medida = If(HASONEVALUE('Futebol-Club'[IDCLUB]);[DistinctClub];[GrandTotal])

 

 




Lima - Peru

Hello @Vvelarde, your solution won't work .. if you use " filter " with 'Inscrições'[ID TIPO FUTEBOL])=2, dax will show you " A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." .

 

 

Vvelarde
Community Champion
Community Champion

hi @vani

 

In test environment is work maybe you have different model in your data.

 

Fut.png

 

Anyway, i hope you can solve this issue. 😃




Lima - Peru

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors