Looping Iterated Values in Power BI

[mayankkverma95]

Hi All,

I have two columns in Fact table (Column A- Sales, Column B-Customer Age).

Requirement:

I have to make a third column (basically a range column) which will help me to get customer age segmentation (0-10 yrs, 10-20 yrs....) dynamically.

What this dynamically means, If user select (like i have values using What If Paramater, from 0-20 Whole number) any value, say 10, then my third column should be like 0-10, 10-20, 20-30 and so on

and If user select value 15 (in what if paramenter) then the third column should be like 0-15,15-30,30-45 and so on...

I have a logic already but not able to implement it using Power Query (M) and what if parameter together.

Logic:

If User Selected Value = 1 or 2 or 3 or 4 or 5 (basically he can select any value in What If.)

First Bin (group):
Lower Range of Age = 0
Upper Range of Age = Whatever the user has selected the value in What iF,

So, if selected value is 5 then my First Bin (group) wil be 0-5

Second Bin
Lower Range of Age for the Second Bin= Upper Range of Age (of First Bin)
Upper Range of Age for the Second Bin = Whatever the user has selected the value in What iF + Lower Range of Age for the Second Bin

In my case, 5-10 (if user has selected 5)

Third Bin
Lower Range of Age for the third bin= Upper Range of Age (of second bin)
Upper Range of Age for the third Bin = Whatever the user has selected the value in What iF + Lower Range of Age for the third Bin

.

Similarly for other groups (bin) if any.

There is an iteration from second bin onwards.

Thank you in advance.

I would welcome any other ideas too to get the requirement done.

@PowerQuery @MQuery 

[Jimmy801 · Accepted solution]

Hello @mayankkverma95 

you cannot do that in power query. However you can make that in dax with a measure.

Create 2 new tables... one for you selection 10, 15, 20 etc and a table for lookup where you have every combination for groups and ages and your relative group name like 10-20 or 15-30. Then create a new measure that uses lookupvalue to retreive your desired data. In the appendix you can find a easy example

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[CNENFRNL]

@mayankkverma95 , no matter columns in Power Query or calculated columns, they are all irresponsive to users' selection; but you might apply such age segmentations to calculations in DAX instead of static columns.