Is there a way i can modify this DAX query to improve the performance

[Kunalkawale]

Hi All, I am using this logic is there a way if I can improve the performance of the report.

Table final =
var policy1 = (SELECTCOLUMNS(FILTER('TABLEBI',TABLEBI[ColumnA] in SELECTCOLUMNS (FILTER('TABLEBI',TABLEBI[ORIGINAL] in SELECTCOLUMNS (  FILTER ( 'TABLEBI',TABLEBI[POLICY]= "VALUE"),"T1" ,TABLEBI[ORIGINAL] ) && TABLEBI[ORIGINAL] <> ""),"Pol", TABLEBI[ColumnA])&& TABLEBI[ColumnA] <> ""),
"policy",TABLEBI[POLICY]) )

var policy2 = (SELECTCOLUMNS(filter(TABLEBI,TABLEBI[ORIGINAL] in (SELECTCOLUMNS(FILTER(TABLEBI,TABLEBI[POLICY] in (SELECTCOLUMNS(FILTER( TABLECL,TABLECL[COLUMNB] in (SELECTCOLUMNS ( FILTER ( 'TABLECL', TABLECL[POLICY]="VALUE"),"COLUMNC" ,TABLECL[COLUMNB]))),"policy",TABLECL[POLICY]))),"org",TABLEBI[ORIGINAL]))),
"pol",TABLEBI[POLICY]))

return (

SUMMARIZECOLUMNS(

'TABLECL'[POLICY],

'TABLECL'[COLUMNC],

'TABLEBI'[ORIGINAL],

'TABLEBI'[PreviousPOLICY],

'TABLEBI'[ColumnA],

'TABLECL'[COLUMND],

'TABLECL'[COLUMNB],

filter(All(TABLECL),TABLECL[POLICY] in policy1
||
'TABLECL'[POLICY] in (SELECTCOLUMNS(FILTER(TABLEBI,TABLEBI[ColumnA] in (SELECTCOLUMNS(FILTER(TABLEBI,TABLEBI[POLICY] in policy2),"pol",TABLEBI[ColumnA])) && TABLEBI[ColumnA] <> ""),"TABLEBI",TABLEBI[POLICY]))

||
TABLECL[POLICY] in policy2
||
TABLECL[POLICY] in
(SELECTCOLUMNS(FILTER(TABLECL,TABLECL[COLUMNB] in (SELECTCOLUMNS(FILTER(TABLECL,TABLECL[POLICY] in (SELECTCOLUMNS(FILTER(TABLEBI,TABLEBI[ORIGINAL] in  (SELECTCOLUMNS(FILTER(TABLEBI,TABLEBI[ColumnA] in SELECTCOLUMNS (FILTER('TABLEBI',TABLEBI[POLICY] in policy2),"Pol", TABLEBI[ColumnA])&& TABLEBI[ColumnA] <> ""),"policy",TABLEBI[ORIGINAL]) )),"pol",TABLEBI[POLICY]))),
 "insured",TABLECL[COLUMNB]))), "policy",TABLECL[POLICY]))
),

"AMOUNT", 'TABLEBI_Premium'[AMOUNT],

"TotalVALUE", 'TABLECL'[TOTALVALUE]
 )
)

[some_bih]

Hi @Kunalkawale  one of possible solution is below (using AI) 🙂 I hope this help at least like idea

VAR Policy1 =

CALCULATETABLE(

        VALUES('TABLEBI'[POLICY]), 'TABLEBI'[ORIGINAL] <> "",

      'TABLEBI'[POLICY] = "VALUE", 'TABLEBI'[ColumnA] <> ""

)

VAR Policy2 =

          CALCULATETABLE( VALUES('TABLEBI'[POLICY]),

         'TABLEBI'[ColumnA] <> "", 'TABLEBI'[POLICY] IN ( CALCULATETABLE( VALUES('TABLECL'[COLUMNB]),         'TABLECL'[POLICY] = "VALUE" ) ) )

RETURN

SUMMARIZE( FILTER( CROSSJOIN('TABLECL', 'TABLEBI'),

      ( 'TABLECL'[POLICY] IN Policy1 || 'TABLECL'[POLICY] IN Policy2 || 'TABLECL'[COLUMNB] IN Policy2 ) &&                        'TABLEBI'[ColumnA] <> "" ), 'TABLECL'[POLICY], 'TABLECL'[COLUMNC], 'TABLEBI'[ORIGINAL],                      'TABLEBI'[PreviousPOLICY], 'TABLEBI'[ColumnA], 'TABLECL'[COLUMND], 'TABLECL'[COLUMNB], 'TABLEBI_Premium'[AMOUNT], 'TABLECL'[TOTALVALUE] )