How to use COUNTIF in Tableau

Question

What I have? I am having a simple COUNTIF task in Excel

This is the data:

ID   Metric   Scope   DynamicCalc
1    A1       TRUE    X
1    B1       FALSE   X
2    B1       TRUE    X
2    A1       FALSE   X
2    C1       FALSE   X
 

What I want?

I am facing issues in replicating the same COUNTIF task in tableau.The column 'DynamicCalc' should have the following values when Metric=A1 is selected: TRUE,TRUE,FALSE,FALSE,FALSE but in case B1 is selected it would be FALSE,FALSE,TRUE,TRUE,TRUE... so basically I want to assign a value of TRUE to the DynamicColumn if there is at least one TRUE in the Scope column to all rows for that ID.

Answer 1

You can use the following LOD Expression to retrieve your desired result. Try using a calculated field like below:

{FIXED [ID],[Metric]:MAX(if [Scope]='TRUE' then 'True' else 'False' end)}

When the Selection is B1 :

Answer 2

Use this lod expression:

{FIXED [ID],[Metric]:MAX(if [Scope]='TRUE' then 'True' else 'False' end)

Answer 3

If you're using tableau version 9 or above, you can use different LOD expressions to achieve this.

Answer 4

You can use LOD expression for this purpose. LOD Expressions provide a way to easily compute aggregations that are not at the level of detail of the visualization. You can then integrate those values within visualizations in arbitrary ways. 

Answer 5

There are three types of LOD expressions. 

Fixed - FIXED level of detail expressions compute a value using the specified dimensions, without reference to the dimensions in the view.

Include - INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.

Exclude - EXCLUDE level of detail expressions declare dimensions to omit from the view level of detail

Answer 6

I'll give you a brief idea on how to create LOD Expression:

1. Select Analysis > Create Calculated Field.

2. In the Calculation editor that opens, do the following:

   • Name the calculation, Sales Per Customer.

   • Enter the following LOD expression:

     { INCLUDE [Customer Name] : SUM([Sales]) }

3. When finished, click OK.

   The newly created LOD expression is added to the Data pane, under Measures. 

For an in-depth explanation check out Tableau Training Course details.