Switch statement to return chat values

[Anonymous]

hello all,

i have this sample data in Power BI:

Users	Salesmembers	Managers	Cars	Phones	Expenses	pagers
10,205	1,500	500	2,000	2,000	1,503	1,500

What I am trying to create is a chart like this:

What I have done is created a disconnected table with all the categories and placed that measure in the axis.

Category

Users

Salesmembers

Managers

Cars

Phones

Expenses

pagers

Now I need to use a DAX meaure to place in the visual to return the correct values.

how can I do this with DAX? 

[Anonymous · Accepted solution]

yes! thank you so much

[smpa01]

I am not sure whether you wanted PBI to render the chart or want a switch statement. PowerBI can return the same chart without needing a switch at all.

[Anonymous]

the reason I wanted to use a switch statement is because I have a lot of other data in my file and I dont want to mess up any other visuals if I were to pivot this data

[smpa01]

Does this work for you?

[Anonymous]

@smpa01  I spoke too soon:

is it possilbe to use a switch statement when the data is formatted like this:

Users	Salesmembers	Managers	Cars	Phones	Expenses	pagers
10,205	1,500	500	2,000	2,000	1,503	1,500

not pivoted 

[smpa01]

As long as the categories in the source data are within a single column the measure will work as planned even if you plan to include those categories as columns in the viz.

But if the categories are separate columns within the source data, they need to be pivoted first to cluster in a single column for you to achieve what you need.

[Anonymous]

got it. My last question would be is it possible to create a seprate table pulling in only the data i need from the origional table? 

meaning only pulling this data:

Users	Salesmembers	Managers	Cars	Phones	Expenses	pagers
10,205	1,500	500	2,000	2,000	1,503	1,500

into a new table so I can pivot

thank you so much for the help

[smpa01]

@Anonymous  can you please provide me a source data which would have other undesired data and the final output that you desire.

[Anonymous]

Sure one moment. How can I add a file to this thread ? I dont see where I can drop a file

[smpa01]

you can share a file through G/1 drive

[Anonymous]

here is the pbix file with the sample data. https://drive.google.com/open?id=1W6IiXgXpEGylUPt-bUIln7uakSDb_Ax6

I cannot pivot the data because i am using the other data for other visuals.

this is what I would like to accomplish :

[smpa01 · Accepted solution]

One thing, for you to display the CATEGORY level - managers, cats etc etc on the X axis, DAX would need a table that contains those values in a single column.

You can either achieve it by creating a table on-the-fly like I did and have the visual created form table 2 or by creating a disconnected table as DCN in the example and then SWITCH !!!! 😂😂

[Anonymous]

@smpa01  is it possible to only sumarize the most current date and not all the dates?

[smpa01]

yes possible

[Anonymous]

thank you, this is perfect 

[Anonymous · Accepted solution]

yes! thank you so much