Calculated Column Creation based on Column Values

[Shri G]

Hello All,

I have to create calculated column to display specific values of one column. Please refer below sample data :

I have a column 'TYPE' having 10 unique values. I have to create calculated column to display only 3 values of TYPE column. I have tried with CASE expression but except required values other values of the column are also getting added as Empty. I have to create Bar visualization based on this calculated column and COUNT OF USER. But Empty is also getting added in charts. When Filtered, data is getting impacted.

Calculated Column :

Case when [TYPE]='SUBMIT' then 'SUBMIT'

when [TYPE]='LINK' then 'LINK'

when [TYPE]='ACCESS' then 'ACCESS' END

Count of Users : Count([TYPE]) over [uSER]

Can somebody Please help me ?

Thanks in advance!

[Jose Leviaguirre]

To address the empty values, you can try something like this

CASE 
    WHEN [TYPE] = 'SUBMIT' THEN 'SUBMIT'
    WHEN [TYPE] = 'LINK' THEN 'LINK'
    WHEN [TYPE] = 'ACCESS' THEN 'ACCESS'
    ELSE 'Other'

The calculated column can't filter empty values. Only your filters can, unless you create a data function that outputs a table

To clarify further, what would be the expected calculated column output? Can you hard code it? Will it return the same number of rows? Do you want to ignore those cases where the case statement doesn't fall under any case? What's the logic (regardless on the custom expression syntax) for each row?

You can also try something like this to address each one of your 10 cases and "group" them into only 3 categories

Case [type]
When "REGISTRATION" then "SUBMIT"
When "HELP" then "SUBMIT"
When "ACTION" then "LINK"
When "SERVICES" then "LINK"
When "DATA" then "ACCESS"
When "ORDER" then "ACCESS"
When "CREDIT" then "ACCESS"
Else [type] end

 

[Shri G]

Thank you @Jose Leviaguirre​ for your reply!

As per your suggestion, I think we can go with table creation. Can you help me how this tables can be created through data functions.

Table 1 : It should contain all the columns with TYPE 'Submit,Link,Access' only

Table 2 : It should contain all the columns with TYPE 'Data, Action' only

Based on these tables I can create visualization to avoid that empty and other values

Can you please help with data function please?

[Jose Leviaguirre]

Hello Shri, Let me step back for a second. You want to create a visualization that counts the users but ignores the blanks?

Try limiting the data of your visualization using this expression:

[TYPE]="SUBMIT" 
or [TYPE]="ACCESS" 
or [TYPE]="LINK"

[Shri G]

Hi Jose,

Yes, I want to create visualization that counts only these 3 values and ignores the empty/other values.

Empty values, here in bar chart is nothing but the other types. I wanted to remove that. I have used "Show/Hide Items"

option of properties. It did work for me.

I can now see required values in cross table and bar charts.

Thank you for all your valuable inputs! :)