Custom columns based on IF statement

[Anonymous]

Hi, 

I am trying to build a dashboard of which shows the amount of files i have in a storage tier. I have exported over 1 million files into a CSV with the last accessed date and also the size of the files as a property. My goal is to create a column which will specify 'Hot' if the file is less than 1 year old, 'Cold' if the file is 1-3 years old, and 'Archive' if the file is older than 3 years. I have done this with a conditional column in Query Editor.

However, when i apply the changes to the dashboard, i would like to have 3 'Card' visualizations that show how much space is being used for each storage tier (Hot,Cold,Archive). For example, if i have 1000 hot files of which result to 17gb in total size, this would be reflected in the card visualisation as 'Hot = 17GB'.

At the moment i have 1 column which has Hot, Cold next to the relevant files. Although when i add the card visualization it just shows all of the values in the column, where as i'd like to filter it for each visualization.

Could someone let me know the best way to do a total of the storage for each hot, cold, archive and how to show it in the visualization? 

Thanks in advance.

Matt

[Anonymous]

[JIGAR]

@Anonymous 

You can have 2 approaches to this. 

1. Have 3 card visualizations and show individual storage type & storage for Hot, Cold and Archive

2. Have 1 card visual and show storage type & storage based on the selection from the filter. 

Please find the link below if you want to have a look at the sample code. 

https://drive.google.com/open?id=1u313aJC60YukV8Xt8-nwxKqDWUb2wJto

Hope this helps !! 

Please mark the answer as accepted if you find the solution appropriate. 

Thanks

[Anonymous]

Hi @JIGAR , very helpful thank you.

How did you do the solution number 2?

If i choose the card visual, and then choose size column and the column i created, it shows all archive cold and hot. How do you filter it so the visual card only shows 1 chunk of it?

Thank you,

Matt

[Anonymous]

Sorry i mean how did you do the 3x cards solution?

---

---

[JIGAR · Accepted solution]

I have used 3 different measures for solution no 2.

You can download the pbix file and have a look at the implemented solutions. I have shared the URL along with my solution.

[Anonymous]

Thank you Jigar, i have got this working.

Accepted as solution.