Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 3-4 tables which are joined together
Table1 ---- Table 2
Table1 --- Table 3
Both relationships are 1:1 and filters both ways. What is a better way to do something like this. It does not come back at all. SQL subquery does come back in 4-5 minutes. What am I doing wrong. Please help.
CALCULATETABLE(
SUMMARIZE(
CALCULATETABLE(
SUMMARIZE(
TABLE1,
TABLE1[column1],
TABLE2[column1])
),
FILTER(TABLE2, TABLE2[DATE] > TODAY() - 16, RELATED(TABLE3[COLUMN1] = "CLOSED")
),
TABLE1[column1],
TABLE2[column1]
),
FILTER(TABLE2, TABLE2[DATE] > TODAY() - 16, RELATED(TABLE3[COLUMN1] = "SETTLED")
)
Solved! Go to Solution.
This returns a table which can't be the final result of a measure. You would have to create a new table with this formula or do a calculation over it:
SUMX(
Filter(
SUMMARIZE(
FILTER(TABLE1, Related(TABLE2[DATE]) > TODAY() - 16, RELATED(TABLE3[COLUMN1] = "CLOSED") ,
TABLE1[column1],
TABLE2[column1]),
TABLE2[DATE] > TODAY() - 15, RELATED(TABLE3[COLUMN1]) = "SETTLED"),
SUM([anycolumn])
)
You could try
SUMMARIZE(
FILTER(TABLE1, Related(TABLE2[DATE]) > TODAY() - 16, RELATED(TABLE3[COLUMN1] = "CLOSED" || RELATED(TABLE3[COLUMN1] = "SETTLED") ,
TABLE1[column1],
TABLE2[column1])
)
Use && if you need it to be closed AND settled. The Summarize and Filter functions both return a table.
Thanks for the reply. I'll remodel my solution with Filter
APologies I made a mistake with the filter.
First filter
FILTER(TABLE1, TABLE2[DATE] > TODAY() - 16, TABLE2[COLUMN1] = "CLOSED")
Second Filter
FILTER(TABLE1, TABLE2[DATE] > TODAY() - 16, RELATED(TABLE3[COLUMN1] = "SETTLED")
Basically I am modelling this subquery
Select t.column1, t.column2, t1.column3, t2.column1, t3. column1
from t3 inner join t2
inner join (
Select
table1.column1, table1.column2, table2.column1
from
table1 inner join table2
on some id
where
table2.date > currentdate - 16
and
table2.cat = closed) t1
where
t2.date > currentdate - 15 and t3.cat = settled
A little confused for the need of the sub query when you could just throw the conditions all in just one where clause.
However, you can continue nesting the Filter and Summarize statement in another Filter Statement as so if needed:
Filter(
SUMMARIZE(
FILTER(TABLE1, Related(TABLE2[DATE]) > TODAY() - 16, RELATED(TABLE3[COLUMN1] = "CLOSED") ,
TABLE1[column1],
TABLE2[column1]),
TABLE2[DATE] > TODAY() - 15, RELATED(TABLE3[COLUMN1]) = "SETTLED")
Thanks again,
the inner query yields CLOSED accounts. use this as table and join to a couple more tables to get the Settled accounts. the settled filter comes from a totally different table. hope I am making sense.
I am not getting the numbers right, not sure What i am doing wrong.
Filter(
SUMMARIZE(
FILTER(TABLE1, Related(TABLE2[DATE]) > TODAY() - 16, RELATED(TABLE3[COLUMN1] = "CLOSED") ,
TABLE1[column1],
TABLE2[column1]),
TABLE2[DATE] > TODAY() - 15, RELATED(TABLE3[COLUMN1]) = "SETTLED")
if i try the above format, it says a single value for D_CAL cannot be determined and this can happen when measure is not defined without aggregation error.
This returns a table which can't be the final result of a measure. You would have to create a new table with this formula or do a calculation over it:
SUMX(
Filter(
SUMMARIZE(
FILTER(TABLE1, Related(TABLE2[DATE]) > TODAY() - 16, RELATED(TABLE3[COLUMN1] = "CLOSED") ,
TABLE1[column1],
TABLE2[column1]),
TABLE2[DATE] > TODAY() - 15, RELATED(TABLE3[COLUMN1]) = "SETTLED"),
SUM([anycolumn])
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |