Forum Discussion
Need help in performing this query using DAX or power Query editor
Hi Guys,
I have 2 tables:
Table-1:
| Tower Model ID | Min Level | Max Level |
1 | 10 | 20 |
| 2 | 20 | 30 |
| 3 | 10 | 40 |
Table:-2
| Tower Model ID | Level | SRF |
| 1 | 10 | 1.4 |
| 1 | 11 | 1.8 |
| 1 | 17 | 1.9 |
| 1 | 25 | 1.11 |
| 2 | 20 | 1.4 |
| 2 | 21 | 1.4 |
| 2 | 27 | 1.4 |
| 2 | 28 | 1.4 |
| 2 | 35 | 1.1 |
| 3 | 10 | 1.1 |
| 3 | 12 | 1.1 |
| 3 | 20 | 1.1 |
| 3 | 25 | 1.1 |
| 3 | 30 | 1.1 |
| 3 | 45 | 1.1 |
Now mylevels are defined in table 1 and I need to extract the corresponding SRF's from Table -2 between these 2 levels so for example for tower model id 1 I need all SRF values from table 2 from level 10 to level 17 ( SRF values from level 10 to 20 only )
So desired output Table
| Tower MOdel ID | LEVEL | SRF |
| 1 | 10 | 1.4 |
| 1 | 11 | 1.8 |
| 1 | 17 | 1.9 |
| 2 | 20 | 1.4 |
| 2 | 21 | 1.4 |
| 2 | 27 | 1.4 |
| 2 | 28 | 1.4 |
| 3 | 10 | 1.1 |
| 3 | 12 | 1.1 |
| 3 | 20 | 1.1 |
| 3 | 25 | 1.1 |
| 3 | 30 | 1.1 |
Would this help
SRF= Calculate ( table2(SRF) , table1(minlevel)<table2(level) <table1(maxlevel)
but could someone help me with this please?
3 Replies
Anonymous,
This solution requires a one-to-many relationship between the tables:
Create DAX calculated table:
OutputTable = FILTER ( Table2, Table2[Level] >= RELATED ( Table1[Min Level] ) && Table2[Level] <= RELATED ( Table1[Max Level] ) )Thanks a lot DataInsights , Sorry For not correctly explaining my problem earlier but just realized that I have many to many relationship , please see below:
1.) Sorry If I was not clear ,Since I am having a Many-Many relationship I am seeing the below error:
So my table 1 has repeated occurence of towermodelID
Table-1:
Tower Model ID Towermodel Section Min Level Max Level 1
1_1 10 20 1
1_2 21 25 1
1_3 26 30 2 2_1 20 30 2 2_2 31 40 3 3_1 10 40 3 3_2 41 50 Table:-2
Tower Model ID Level SRF 1 10 1.4 1 11 1.8 1 17 1.9 1 25 1.11 2 20
1.4 2 21 1.4 2 27 1.4 2 28 1.4 2 35 1.1 3 10 1.1 3 12 1.1 3 20 1.1 3 25 1.1 3 30 1.1 3 45 1.1 Table 3: intermediate output
Tower MOdel ID Towermodel Section LEVEL SRF 1 1_1 10 1.4 1 1_1 11 1.8 1 1_1 17 1.9 1 1_2 25 1.11 2 2_1 20 1.4 2 2_1 21 1.4 2 2_1 27 1.4 2 2_1 28 1.4 2 2_2 35 1.11 3 3_1 10 1.1 3 3_1 12 1.1 3 3_1 20 1.1 3 3_1 25 1.1 3 3_1 30 1.1 3 3_2 45 1.1 Finally I need the below output ( Now for a normal table I can open this in query editor and do aggregation but if it is a calculated table then how to proceed ?)
Final Table output desired : I need the min SRF in each of the towermodel section
Table 4:Final Output
Tower MOdel ID Towermodel Section LEVEL MIN SRF 1 1_1 10 1.4 1 1_2 25 1.11 2 2_1 20 1.4 2 2_1 21 1.4 2 2_1 27 1.4 2 2_1 28 1.4 2 2_2 35 1.11 3 3_1 10 1.1 3 3_1 12 1.1 3 3_1 20 1.1 3 3_1 25 1.1 3 3_1 30 1.1 3 3_2 45 1.1 2..) I was hoping to know if we can convert this calculated table as a normal table that can be opene din query editor because I need to some more joins and aggregations on top of this table.
