Forum Discussion
Need help in performing this query using DAX or power Query editor
Guys could someone please help me on this ?
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.
