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'm trying to dynamically change the axis data in a Line chart with a slicer.
So, when I select Months, Year Quarter, or Rolling Quarter, it'll show the same data with another time dimension. Something like this
I have already created a table with the data i need
But now what I have is this, but I'm stuck and can't make it work. The chart doesn't allow me to use this measure in Axis, and the measure is not working at all.
Any ideas?
Solved! Go to Solution.
Hi @IrsFreire ,
You can refer this article about creating dynamic X-axis in power bi:
Dynamic dimensions in Power BI
To achieve dynamic X-axis, you need to create a bridge table to crossjoin the dimension column and the Date column. Create a calculated table like this:
Bridge table =
SUMMARIZE ( CROSSJOIN ( 'Table', 'Time_Dimension' ), [Date], [Time_Dimension] )
Follow the above article to create relationships between these tables and create a new calculated column based on this calculated table:
DimensionColumn =
SWITCH (
FIRSTNONBLANK ( 'Time_Dimension'[Time_Dimension], 1 ),
"Months", RELATED ( 'Table'[Months] ),
"Year Quarters", RELATED ( 'Table'[Year Quarters] )
)
Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @IrsFreire ,
You can refer this article about creating dynamic X-axis in power bi:
Dynamic dimensions in Power BI
To achieve dynamic X-axis, you need to create a bridge table to crossjoin the dimension column and the Date column. Create a calculated table like this:
Bridge table =
SUMMARIZE ( CROSSJOIN ( 'Table', 'Time_Dimension' ), [Date], [Time_Dimension] )
Follow the above article to create relationships between these tables and create a new calculated column based on this calculated table:
DimensionColumn =
SWITCH (
FIRSTNONBLANK ( 'Time_Dimension'[Time_Dimension], 1 ),
"Months", RELATED ( 'Table'[Months] ),
"Year Quarters", RELATED ( 'Table'[Year Quarters] )
)
Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yingjl
Thanks for the solution. Have already accepted as the solution, but now if you can, the other problem is to create the rolling quarters 😅 Month and Quarter working fine.
But I need another dimension to return the accumulate of every 3 months, ending on the lastest data. So, i´ll not be the year quarter.
If the latest data is February, I need:
1. Dec, Jan, Feb
2. Sep, Oct, Nov
3. Jun, Jul, Aug
4. So on and so forth
@IrsFreire ,Dynamically change chart axis in Power BI , first option is bookmark -https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
Other is unpivot table
https://www.youtube.com/watch?v=6jeSIRpjv0M
With direct query
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |