Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (17.6k points)

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

UserDecJan
Total317
Thomas19
John28

 

1 Answer

0 votes
by (47.2k points)

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

...