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

                            )

 

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

     

5 Replies

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