Dynamically change X Axis values based on Slicer and help regarding card visuals/alternatives.

[joshmahe]

Fairly new to PowerBI, facing two issues with bar charts. Any help is welcome. 

 

1. I need the X axis values to change based on a drop down selection. The above chart shows date ranges which correspond to a year, I have created a table like below and related it with my main table year column which I use in the X axis in above chart. My issue is when my slicer displays the index column and I select 2, instead of just displaying year 2020, it should display both 2019 and 2020. If I select 3, it should display that selected year(2021) and previous years(2020, 2019) in the x axis and so on. I am not able to achieve that, pls give me a solution. Should i change my below table or use a dax or filters? I am not sure. 

Index	Year
1	2019
2	2020
3	2021
4	2022
5	2023
6	2024

2. Second, i am not able to use card visuals to display the small grey box with percentage that is at the bottom of each bar in the above chart. I need that value to update based on the year it sits on. I have a formula with which its calculated but card visual is just to display static measures, nothing dynamic is happening. Please tell me if you know of alternatives and how to achieve that.  I tried using line and clustered chart but the values are moving based on the where the line markers are, but i want it to remain at the bottom of each bar like in the image. 

TIA. 

[saudansari]

For your 1st concern

Create a Relationship:

Make sure there is a relationship between the "Year" column in your main table and the "Year" column in the Index table. This relationship will enable filtering based on the selected year.
Create a Slicer:

Add a slicer to your report based on the "Index" column from the Index table.
Create a DAX Measure for X-axis:

Create a new DAX measure that dynamically calculates the X-axis values based on the selected Index. Use the following formula:

X-axis =
VAR SelectedIndex = SELECTEDVALUE(IndexTable[Index])
RETURN
CALCULATETABLE(
VALUES(MainTable[Year]),
IndexTable[Index] <= SelectedIndex
)
This measure uses the CALCULATETABLE function to filter the distinct years in the MainTable based on the selected Index in the slicer. It ensures that all the previous years are included in the result.

Use the Measure in X-axis:

Instead of directly using the "Year" column from the MainTable in your chart, use the newly created "X-axis" measure in the X-axis of your chart.
Now, when you select an Index in the slicer, the X-axis will dynamically adjust to show the selected year and all the previous years.

Note: Adjust table and column names in the formulas according to your actual Power BI model. The key here is to use CALCULATETABLE to dynamically filter the years based on the selected Index.