How to create a calculated column based on values of other columns

[Anonymous]

Hi Team,

I have a table which have some 9 columns . Out of 9 Columns , 3 Columns are used in report. Those 3 columns are named as Hardware Management controller, LPARS and Managed Systems . Each of this columns hold multiple values . Now my stake holders asked to add a slicer which should have values as this 3 column headers . Visuals are shown below . 

When I click on Hardware Management controller in slicer, Only values which belongs to this column should only be shown . it shoud be same with other values in slicer as well. 

Please advise me . 

THanks,

G Venkatesh 

[Mariusz]

Hi @Anonymous 

Please follow the steps below

1. Go to Transform Ribon in Query Editor

2. Select your columns.

3. Click on Unpivot Columns.

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Mariusz,

Thanks for your quick reply. This is what I am looking for . 🙂 

In Addition to this, I actually forgot to mention in my previous post , I need an advise on more item pls. Please find the attached screen shot for more understanding of data. 

Now that I have 2 slicers ,i.e are Attribute and other is value which are created based on your previous post. If I select Servers/LPARS, it is showing only Values for Servers/LPARS which is absolutely correct. But when I select some thing from Values slicer, user wants to know related Managed system values and Hardware Management controller values. 

I know this isnt a correct way to show,but can we tweak it in any way ? Please suggest ..

[Mariusz]

Hi @Anonymous 

Does all the data come from one table, if not please can you shere the screenshot of your model

Thanks 
Mariusz

[Anonymous]

Hi Mariusz,

Yes. All the data comes from one single table . Please find the screenshot of model.

[Mariusz]

@Anonymous 

Not sure if I fully understood the requirement, but I think that you need to bring this table twice in original form and Unpivoted as you just did, than link it on Instance_id both directions.

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Mariusz,

Sorry that I couldnt able to reply to you because of not being available at office. Sorry about the confusion . Let me explain you the problem quickly . 

My Customer needs a slicer with the column headers(names) as values which we have created based on your Suggestion ( unpivot columns) . I was able to successfully do that . Then, I have created a table and then brought all those values together . See the formuale below. 

Table =

DISTINCT (

UNION (

SELECTCOLUMNS ( 'Server Dependency', "Attribute",'Server Dependency'[Low Level]),

SELECTCOLUMNS ( 'Server Dependency', "Attribute",'Server Dependency'[Mid Level] ),

SELECTCOLUMNS ( 'Server Dependency', "Attribute",'Server Dependency'[Top Level] )))

Post this, I have dragged this Column "Attribute" into a slicer and started filtering data based on selection. However, this filter is not working properly .   Slicer picture has been uploaded here for reference. 

For example if I select Server/LPARs , i have to get only those CIs . However, I am receiving CIs that are from Hardware Management Controller and Managed System as well. 

I am not able to find the reasons. Is it because of all this data coming from single query with various conditiions for each type of CI ?

Kindly help !! 

[Mariusz]

Hi @Anonymous 

Can you share pbix file?

Thanks

Mariusz