cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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

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.

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.

Helper III

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...

Employee

@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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors