Jump to content
We've recently updated our Privacy Statement, available here. ×

Calculated Column Creation based on Column Values


Shri G

Recommended Posts

Hello All,

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

image.png.a2206ecc4815719d7bcdad62cf2ab1c7.pngI 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]

image.png.9d75c3c083e161624d03a22d0790a1d1.pngCan somebody Please help me ?

Thanks in advance!

Link to comment
Share on other sites

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'END

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
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...