topic Re: How to use measure as X asis in column chart in DAX Commands and Tips

How to use measure as X asis in column chart

Anonymous — Wed, 24 Jan 2024 05:10:51 GMT

Dear all,

I wrote the measure to caculate aging my measure work well. However I can not put the measure to row or column of the chart or pivot table. Could you please advise if any solution for my case?

aging_caculate = var _date_select = SELECTEDVALUE(date_table[Date]) var a = CALCULATE(SUMX(Table1, SWITCH(TRUE(), DATEDIFF(Table1[date],_date_select,DAY)<30,1, AND(DATEDIFF(Table1[date],_date_select,DAY)>30,DATEDIFF(Table1[date],_date_select,DAY)<60),2, AND(DATEDIFF(Table1[date],_date_select,DAY)>60,DATEDIFF(Table1[date],_date_select,DAY)<90),3, 4 ))) RETURN a

pbix file here

Re: How to use measure as X asis in column chart

123abc — Wed, 24 Jan 2024 06:52:17 GMT

It seems like you are trying to create a measure to calculate aging and want to use this measure as the X-axis in a column chart or pivot table. However, measures in Power BI are typically used in the Values area of a visual, not directly in the rows or columns.

To use your aging measure in a column chart or pivot table, you need to create a dimension that represents the categories on the X-axis. In your case, it seems like you want to categorize the aging into different buckets (e.g., 1-30 days, 31-60 days, 61-90 days, etc.).

Here's how you can achieve this:

Create a new table or use an existing one to represent the aging buckets. You can use the following DAX code to create a calculated column in this table:

AgingCategory =
SWITCH(
TRUE(),
[aging_caculate] = 1, "0-30 days",
[aging_caculate] = 2, "31-60 days",
[aging_caculate] = 3, "61-90 days",
[aging_caculate] = 4, "More than 90 days",
"Unknown"
)

This code uses the result of your aging_caculate measure to categorize the aging into different buckets.

Now, use the new AgingCategory column as the Axis field in your column chart or pivot table.

Here's a step-by-step guide:

Open the report where you want to create the chart.
Drag and drop the AgingCategory column to the Axis area of your visual (column chart or pivot table).
Drag your aging_caculate measure to the Values area to show the calculated aging.

This way, you'll have a column chart or pivot table with your aging measure on the Y-axis and the aging categories on the X-axis.

Remember to adjust the code and column names based on your actual table and column names.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: How to use measure as X asis in column chart

Anonymous — Wed, 24 Jan 2024 09:51:14 GMT

hi 123abc ,

Thank you for your solution, In my measure I have a date_selection in to pick the date I want to caculate the aging.

var _date_select = SELECTEDVALUE(date_table[Date])

Following your comments, I have to create new virtual table with a column base on the value of measure above , right?

Re: How to use measure as X asis in column chart

123abc — Thu, 25 Jan 2024 06:34:49 GMT

Yes, you're correct. If your measure includes a variable _date_select that is based on the selected date in a slicer or filter, you can create a new table that includes the unique values of your measure. This will allow you to use that table for your X-axis in a column chart.

Here's how you can modify the previous instructions:

Create a Table Visualization:
- Go to the "Data" view in Power BI.
- Click on "New Table" in the "Modeling" tab.
- Enter the following DAX formula to create a new table:

AgingTable = VALUES('YourTableName'[aging_caculate])

- Replace 'YourTableName' with the actual name of your table.
Create a Column Chart:
- Go back to the "Report" view in Power BI.
- Drag the "aging_caculate" field from your new "AgingTable" to the "Axis" or "X-axis" well of your column chart.

By creating the "AgingTable" using the unique values from your "aging_caculate" measure, you are essentially creating a table that represents the distinct categories of aging based on the selected date.

Remember to ensure that your chart is interactive and that it reacts to changes in the slicer or filter that affect the _date_select variable in your measure. This way, the chart will dynamically update based on the selected date.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: How to use measure as X asis in column chart

Anonymous — Thu, 25 Jan 2024 07:23:47 GMT

Hi 123abc ,

Thank you for your advice, I tried with above suggestion however It show the error as below screenshot.

Could you please help check what I make some wrong ? It stuck here so I can not process for the next step.

my pbix file here

Re: How to use measure as X asis in column chart

Anonymous — Thu, 25 Jan 2024 07:39:21 GMT

hi 123abc ,

Thank you for your guidance, I tried to create virtual table contain only values from the measure, however I got the error as the screenshot below, could you please help check what wrong with my formula ?

Here is my pbix file