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.
Hi,
I'm sure this should be simple but I'm not a coder and am struggling to get my head around the DAX logic I think I need.
Consider the Transport table below -
ID | Vehicle | Mode |
1 | Car | Private |
2 | Bus | Public |
3 | Lorry | Private |
4 | Boat | Public |
5 | Motorbike | Private |
If I'd like to drop the vehicle field into a table but filter it to only show the private mode options how would I need to write the Dax code please? I know I can simply add a filter button that the user can click on but I'm trying to put a number of different columns into one table with each column showing specific data based on the mode.
Also I don't want count them or anything, I just want the column to show the data but exclude the relevant modes in each column. So in the table visual it woud show one column saying car, lorry, motorbike and then another column showing bus, boat.
Hope that makes sense?
Regards, Jason.
Solved! Go to Solution.
go to power query editor mode (Edit Queries or Transform Data buttons)
Then in your Data Source remove ID column. then pic to Mode column and press to button Pivot Column in Transform mode and set parameters in Dialog window as
Hi @Jasonvl ,
First, use "Pivot column" feature:
If you want to modify the data, please refer to how to merge rows:
Or you can create a measure:
Measure 2 =
CALCULATE(
MAX(Sheet3[Vehicle]),
ALLEXCEPT(
Sheet3,
Sheet3[Mode],Sheet3[ID]
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for the reply.
My issue with that is that it filters the whole visual on the selected mode whereas I want it to only filter one column and then have another column with a different mode filter etc.
So I end up with something like the below -
Public | Private |
Car | Bus |
Motorbike | Boat |
Van |
Hi @Jasonvl ,
First, use "Pivot column" feature:
If you want to modify the data, please refer to how to merge rows:
Or you can create a measure:
Measure 2 =
CALCULATE(
MAX(Sheet3[Vehicle]),
ALLEXCEPT(
Sheet3,
Sheet3[Mode],Sheet3[ID]
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
go to power query editor mode (Edit Queries or Transform Data buttons)
Then in your Data Source remove ID column. then pic to Mode column and press to button Pivot Column in Transform mode and set parameters in Dialog window as
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |