Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have master of data range as below.
KM range |
1-60 |
61-80 |
81-100 |
And I have transaction data as below.
Trip | KM |
A | 20 |
B | 30 |
C | 70 |
D | 90 |
Then I would like to add column in transaction data to define KM range as below. How to set the formula?
Trip | KM | KM range |
A | 20 | 1-60 |
B | 30 | 1-60 |
C | 70 | 61-80 |
D | 90 | 81-100 |
Solved! Go to Solution.
@Anonymous , first split the first table in power query to have additional two-column start range and end range , First duplicate the column and then split
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
then add this new column in dax in table 2
maxx(filter(Table1, Table2[KM] >=Table1[Start range] && Table2[KM]<=Table1[end range]),Table1[KM range])
Hi,
Write this calculated column formula in the Data Table.
=CALCULATE(VALUES(range[Range]),FILTER(range,range[Lower KM range]<=EARLIER(data[KM])&&range[Upper KM range]>=EARLIER(data[KM])))
This is how the range table is set up
This is how the result looks
@Anonymous , first split the first table in power query to have additional two-column start range and end range , First duplicate the column and then split
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
then add this new column in dax in table 2
maxx(filter(Table1, Table2[KM] >=Table1[Start range] && Table2[KM]<=Table1[end range]),Table1[KM range])
@Anonymous
1). In your 'Master Data Range' table probably you can adde two more columns, min and max, ere 1 will come in min column, 60 will go to max column.
2). Now you can create calculated field in your transaction data bascially and as per min max column you can build formula.
Hope above makes sense.
Yes I would set min and max column first. But for next step, to lookup KM range base on min and max criteria I didn't know the formula that suit
@Anonymous
Formula is already provided by @amitchandak please test and let us know if still missing something.
It is not working. How can we link between table.
Hi @Anonymous
Based on my test, the formula seems work fine and you can refer this sample file in the below. If you have any other problems. you can share some screenshots for further dicussion.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |