Forum Discussion

maraprik05's avatar
maraprik05
Frequent Visitor
6 years ago
Solved

DAX for subquery

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 ...
Need Help
  • JasonTX's avatar
    JasonTX
    6 years ago

    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])
    )

     

maraprik05's avatar
maraprik05
Frequent Visitor
6 years ago

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. 

 

 

JasonTX's avatar
JasonTX
Resolver I
6 years ago

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])
)