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 IV

## 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
Microsoft 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 IV

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

Microsoft 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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors