Include Conditional formula to a CountIF( Contains()) formula

Question & Answer
, , , ,
1

Looking to add an additional condition in the current formula
distinct_countIf( Traning Name,
Contains( Training Name, " Training Title A")
or
Contains (Training Name, “Training Title B”))
Current barrier: there is a small population who have take both trainings and counting as 2 skewing the Headcount Total.
TRYING TO INCORPORATE either:

  • a AND/ OR condition
  • If >1 then out put 1
2

Hi @belinsky and welcome to the QuickSight community!
You could try something like:

ifelse(contains({Training Name}, ' Training Title A') OR contains({Training Name}, ' Training Title B'), {Training Name}, NULL)

Set it as a distinct count in the field well when adding it to a visual. See if that returns a different value. You can also add it to a LAC_W aggregation.

distinctCountOver(ifelse(contains({Training Name}, ' Training Title A') OR contains({Training Name}, ' Training Title B'), {Training Name}, NULL), [], PRE_AGG)

If this is still causing it to return a number higher than 1, you could try replacing ‘Training Name’ with something that is more distinct to each line, like a Transaction ID or Training ID (if exists).

Let me know if you have any additional questions or if this helps with your case!

3

Hi @belinsky,

Doing the following should do your work

  1. Create something like Training X? to check if the training data includes the ones you are looking for and mark Y if found else N
    ifelse(in(Training, [‘Training A’, ‘Training B’]), “Y”, “N”)

  2. Create a Count Calculated field to perform the count you need
    distinct_countIf({Trainings X?}, {Trainings X?} = “Y”)

4

Thank you found the structure:
Distinct_CountIf(Name,
Contains( Training A) And Contains (Training B)
OR Contains( Training A)
OR Contains( Training B))

1 Like