Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 !!!! 😂😂
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.
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
Does this work for you?
@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
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.
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
@Anonymous can you please provide me a source data which would have other undesired data and the final output that you desire.
Sure one moment. How can I add a file to this thread ? I dont see where I can drop a file
you can share a file through G/1 drive
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 :
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 !!!! 😂😂
@smpa01 is it possible to only sumarize the most current date and not all the dates?
yes possible
thank you, this is perfect
yes! thank you so much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |