Counting max boolean in Tableau using LOD

user_2533090 0
Viewed 363

I have some data that looks like the following, where the same Id is repeated in multiple rows, but different Victory values.

Id Victory
1 FALSE
1 FALSE
1 TRUE
2 FALSE
2 FALSE
2 TRUE
3 FALSE
3 FALSE
3 FALSE

I am trying to count the number of Victory per Id, so if there is a row where Victory is TRUE, then it should be considered a victory.

I have attempted to use a basic calculated field, which I thought would do the trick. However, it counts victory as TRUE right, but victory FALSE includes any Id where FALSE is present.

In other words, the above data would count 3x FALSE, and 2x TRUE, using the following calculated field:

COUNT ( { FIXED [Id]: MAX([Victory]) } )

This is the visualization I get, where I would expect the count of FALSE to only be 1, but is 3.

Tableau visualization

I have tried numerous variations, but just cannot seem to get the desired results.

I am using Tableau 2020.4.

EDIT

I came across two different solutions to my problem, which I have attached screenshots of below. Note that Victory is replaced by WIN and Id is replaced by Row Id, but the example should be clear.

Solution 1

Solution 1

Solution 2

Solution 2

level-of-detail tableau-api aggregate
1 Answers

First, make sure you understand how COUNT([Some Column]) behaves. It doesn't return the number of records that have TRUE in some column, or 1 in some column. It returns the number of records where [Some Column] has a value, any value at all. In other words, it returns the number of records that have a NON-NULL value.

That's why you are getting a higher than expected result. You are expecting COUNT to somehow only count the records where Victory had a particular value, TRUE in your case. As far as COUNT() is concerned, TRUE and FALSE are equivalent. COUNT() only cares whether there is a value at all - as opposed to null.

With that important comment out of the way, I suggest not evening using COUNT() for this case. The expression SUM(INT([Victory])) will tell you how many rows have the value True for the field Victory. That expression works because the type conversion function INT() converts TRUE to 1 and FALSE to 0.

You can either wrap that expression in an LOD calc if you must, or make a calculated field measure defined as INT([Victory]). Place that field on some shelf with the aggregation function defaulting to SUM(), and place ID on another shelf, and you'll have your answer.

Only use an LOD when you actually need to. You have more flexibility - and often better performance - without using an LOD calc.

user_4419790
Related