Index of the actual filtered result set

[TePe]

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?

[v-haibl-msft]

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

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.

[TePe]

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

[v-haibl-msft]

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

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.