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

     

JasonTX's avatar
JasonTX
Resolver I
6 years ago

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.

  • maraprik05's avatar
    maraprik05
    Frequent Visitor
    6 years ago

    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 

     

    • JasonTX's avatar
      JasonTX
      Resolver I
      6 years ago

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

       

      • 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.