Age Profile - Direct Query

[johnbradbury]

It would be really great if someone could help me with this.

I'm querying my DB directly and I'm trying to group open transactions into an age range. I've created a calculated column which gives me the age in days, now I want to group the results:

0-5 Days

6-20 Days

21-50 Days

>50 Days

It would also be useful if I could order the results so it always shows in the order above.

[v-ljerr-msft · Accepted solution]

Hi @johnbradbury,

If I understand you correctly, you should be able to use the formula below to add another calculate column to group the ages into range.

Range =
SWITCH (
    TRUE (),
    'Table1'[Age] >= 0
        && 'Table1'[Age] <= 5, "0-5 Days",
    'Table1'[Age] >= 6
        && 'Table1'[Age] <= 20, "6-20 Days",
    'Table1'[Age] >= 21
        && 'Table1'[Age] <= 50, "21-50 Days",
    'Table1'[Age] > 50, ">50 Days",
    ""
)

---

It would also be useful if I could order the results so it always shows in the order above.

---

As you're using Direct Query mode, you may need to firstly add a new table called "Age Range" like below on your datasource side.

Range	Index
0-5 Days	1
6-20 Days	2
21-50 Days	3
>50 Days	4

And sort the Range column by the Index column with Sort by Column option on Power BI Desktop.

Then you should be able to create a relationship between "Age Range" table and your data table with the "Range" column, and show the Range column on your report(which should be ordered correctly).

Regards

[johnbradbury]

Anyone?