How to use LOD to count distinct number of records based on some condition?

Question

Goal: For a dimension, count the distinct numbers of records if the string has "List Email", otherwise count all records.

I've tried:

IF CONTAINS(ATTR([Subject]), "List Email:")
then ((COUNTD([Subject])))
Else COUNT([Subject])
END

(That works only if the dimension is in the view)

and

CASE CONTAINS(ATTR([Subject]), "List Email:")
WHEN TRUE   then ATTR({ FIXED  [Subject] : COUNTD([Subject])})
ELSE ATTR({ FIXED  [Subject] : COUNT([Subject])})
END

User	Dec	Jan
Total	3	17
Thomas	1	9
John	2	8

 

Answer 1

From the desired output it seems that you want to fix on [User] and MONTH([Date]) (vs fixing on [Subject] as in the code tried in the question).

This field will gives you the desired output. :

User Emails for Month:

{ FIXED [User], MONTH([Date]) : 

    COUNTD(IIF(CONTAINS([Subject],"List Email"),[Subject],NULL))

    +

    COUNT(IIF(NOT CONTAINS([Subject],"List Email"),[Subject],NULL))

}

For each User and Month, count distinct emails that contain List Email and add to that all emails that don't contain List Email.

But if all you need is the grand totals to be 3 for December and 16 for January (i.e., count distinct subject regardless of the user), then LOD calculation is not necessary:

# Emails:

COUNTD(IIF(CONTAINS([Subject],"List Email"),[Subject],NULL))

+

COUNT(IIF(NOT CONTAINS([Subject],"List Email"),[Subject],NULL))