Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TePe
Helper III
Helper III

Index of the actual filtered result set

Let's imagine you want to show a chart where you have orders (like they come in over the days...) and their order volume. You want to show a chart with a trend line to see how oder volume develops over time. You want to filter the orders by different creterias...

So for this to happen you need a numeric value you can put on the X axis. This can't be the order number (because it might not just be an ascending number), it can't be the date, or anything else, it must be something like an index of the actual result set (so the filtered data set, like "This was the first order coming in, this was the second, ... all based on the actual filtering). DAX formulas like RANKX or RANK.EQ don't seem to help, the index always needs to be based on the actual filtering without any gaps...

 

Any idea how to solve this?

5 REPLIES 5
v-haibl-msft
Employee
Employee

@TePe

 

For the two cases I mentioned before, please submit an idea if you’d like them to be supported in the future.

 

Best Regards,

Herbert

jahida
Impactful Individual
Impactful Individual

I might be misunderstanding, but I think the end objective is to have a line chart with Orders on the X axis and Volume on the Y axis, with the X axis sorted by order date.

 

If I'm correct, then the best way to do this is probably to specify a Sort By Column for Orders.

 

Capture.PNG

 

You can sort by Order Date, and then the axis should sort as desired. I can't see any benefit to having the axis displayed as 1, 2... instead of order number or some other unique identifier (other than your concern about sorting). But I don't think the numbering you're talking about is possible in any base visual in PowerBI, since Measures can't be the Axis of a chart and Tables/Columns don't recalculate on filtering.

Well, that would be too easy 😉 The problem is what to put on the X axis... The order number? It's not perfectly in the same order than the date... The date? We want to see each order, not aggregated by date... Sort the oder number by date (as you suggested)? Might work but there is one problem: To enable a trend line the X axis must be a numeric value... The order number is not numeric... And if you would just use "any" number (like an ID you get from the source system) you would face funny issues... Just think about you have the records filtered and you get i.e. record 1,2 and 10 you would have a big gap between 2 and 10 in the chart... So you need a continuous ID... I'm not expecting that any visual will provide me with this ID, it's more that I'm looking for a way how to produce an ID like that in DAX...

 

Thanks anyway...

@TePe

 

I’ve reproduced the issue which you mentioned as below and reported it internally. I’ll update here if there is some feedback.

 

In following two cases, we cannot enable the Trend Line.

  • Data on X-Axis are numeric values with Categorical instead of Continuous type.
  • Data on X-Axis are non-numeric values which sorted by another date/numeric column.

 

Best Regards,

Herbert

jahida
Impactful Individual
Impactful Individual

Oh I missed the trend line bit, fair enough. Yeah you might be cooked... I could write a measure that gave you the result you wanted, but the problem would be that you can't put a measure on the axis of a graph. So... I've got nothing.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.