cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VanCadi Helper IV
Helper IV

Switch statement to return chat values

hello all,

 

i have this sample data in Power BI:

 

UsersSalesmembersManagersCarsPhonesExpensespagers
10,2051,5005002,0002,0001,5031,500

 

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

Picture1.png

 

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? 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
VanCadi Helper IV
Helper IV

Re: Switch statement to return chat values

yes! thank you so much

View solution in original post

Super User I
Super User I

Re: Switch statement to return chat values

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 !!!! 😂😂

 

View solution in original post

15 REPLIES 15
Super User I
Super User I

Re: Switch statement to return chat values

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.

VanCadi Helper IV
Helper IV

Re: Switch statement to return chat values

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

Super User I
Super User I

Re: Switch statement to return chat values

VanCadi Helper IV
Helper IV

Re: Switch statement to return chat values

yes! thank you so much

View solution in original post

VanCadi Helper IV
Helper IV

Re: Switch statement to return chat values

@smpa01  I spoke too soon:

 

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

 

UsersSalesmembersManagersCarsPhonesExpensespagers
10,2051,5005002,0002,0001,5031,500

 

not pivoted 

Super User I
Super User I

Re: Switch statement to return chat values

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.

VanCadi Helper IV
Helper IV

Re: Switch statement to return chat values

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:

 

UsersSalesmembersManagersCarsPhonesExpensespagers
10,2051,5005002,0002,0001,5031,500

 

into a new table so I can pivot

 

thank you so much for the help

Super User I
Super User I

Re: Switch statement to return chat values

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

 

 

VanCadi Helper IV
Helper IV

Re: Switch statement to return chat values

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors