Scope for optimization of DAX Code of measure

[aatish178]

Hi All, I have 2 tables as shown below:

Table1:

Date
2024-07-07
2022-05-26
2022-05-25
2021-12-13
2021-12-12

Table2:

EmpNo  CompCode CompNo     Valid From          Valid To         Salary       KEY_Emp_Cod_Comp
257298      BE           10035        2020-06-05        2021-11-29      2369          257298-BE-10035
257298      BE           10035        2021-11-30        2021-12-12      2370          257298-BE-10035
257298      BE           10035        2021-12-13        2022-05-25      2433          257298-BE-10035
257298      BE           10035        2022-05-26         9999-12-31     2311          257298-BE-10035
There is no direct relationship between these 2 tables., and Table 2 should show only those records where Valid From <= Selected Date of Table 1

On the top of that result , I wanted to calculate a latest version of data based on Max of Valid To date for each Emp No, Comp Code and Comp No unique combination. I have achieved this through below measures:

measure1:

Latest Valid To = var MaxDate=MAX(Table1[Date])

Var keycomb = selectedvalue(KEY_Emp_Cod_Comp)
Var LatestValidFrom=FILTER(ALL('Table2'),Table2[KEY_Emp_Cod_Comp]=keycomb && Table2[Valid From]<=MaxDate)

Return
CALCULATE(MAX(Table2[Valid To]),LatestValidFrom)

Measure2:

Is_Latest_Version = var MaxDate=MAX(Table2[Valid To])
if([Latest Valid To]=MaxDate , 1 ,BLANK())

And Measure 2 I have used in filter of table visual and set it as is not blank

By using above code I am getting expected result but the performance of table visual has decreased to drastic extent.

I would like to know whether there is still any scope in the optimization of above 2 DAX MEASURE CODE.

Plz help

@amitchandak @v-huijiey-msft @gmsamborn @v-kaiyue-msft @v-jialongy-msft  @Ashish_Mathur @quantumudit @quantumudit1

Regards,

AATISH

[Anonymous]

Hi @aatish178 ,

Regarding your question, which columns do you want to remove the filtering effect of using 'All'? You can specify specific columns instead of the entire table 'Table2'.

Something like this.(Suppose you want to remove the filtering effect of column 'KEY_Emp_Cod_Comp')

Measure = 
var MaxDate=MAX(Table1[Date])
Var keycomb = selectedvalue(Table2[KEY_Emp_Cod_Comp])
Return
CALCULATE(MAX(Table2[Valid To]),ALL('Table2'[KEY_Emp_Cod_Comp]),'Table2'[KEY_Emp_Cod_Comp] = keycomb && 'Table2'[Valid From] <= MaxDate)

 If you just want to remove the filtering effect of the column 'Valid From', you can try using the following DAX expression. The internal filter will override the external filter.

Measure = 
var MaxDate=MAX(Table1[Date])
Var keycomb = selectedvalue(Table2[KEY_Emp_Cod_Comp])
Return
CALCULATE(MAX(Table2[Valid To]),'Table2'[KEY_Emp_Cod_Comp] = keycomb && 'Table2'[Valid From] <= MaxDate)

Best Regards,
Wenbin Zhou

[aatish178]

Hi, Thanks for your reply

I want the dax to consider KEY_Emp_Cod_Comp field in the calculation, because then only the calculation will consider unique combination of EmpNo, CompCode and CompNo field and will give the final result, and valid from field should also get consider in the calculation., so if I remove effect of filtering from these 2 fields, whether my measure will give me correct result???

Please help me with your thoughts on this

Aatish 

[Anonymous]

Hi @aatish178 ,

One question, what fields do you use in your visual ?

If there is only column 'Valid From', and there are repeated dates in this column, and the repeated dates correspond to different 'KEY_Emp_Cod_Comp', the 'selectedvalue' function will return a null value, affecting the calculation.

If there are no repeated values, you can use this dax expression.

If I understand wrongly, please provide more information.

Best Regards,
Wenbin Zhou

[aatish178]

Hi Again,

Thanks for your support.

In the table visual I am using all the mentioned fields except key_emp_cod_comp. I have just included this field for your understanding purpose.

And Valid_From column will have duplicate dates but those are for different emp no, company code and comp no. Unique combination together, then in this case will the mentioned expression by you will help???

[Anonymous]

Hi @aatish178 ,

The Table data is shown below:

If I understand correctly, this is your initial solution but will have performance issues.

My idea is to use only the column 'KEY_Emp_Cod_Comp'.

Measure = 
VAR _maxDate = MAX('Table1'[Date])
RETURN CALCULATE(MAX('Table2'[Valid To]),'Table2'[Valid From] <= _maxDate)

Regarding the expression you provided, if the amount of data is very large, I am worried that the use of the ALL function is the main reason for the performance degradation.

Best Regards,
Wenbin Zhou

[aatish178]

Thanks but I can't directly remove key_emp_cod_comp from the measure, because the measure should consider a unique combination of each EmpNo, ComCode And CompNo. In the calculation. 

So if I remove a key column from measure then the expression might not give expected result. It is a kind of group by with 3 columns , and I don't want to use summerize function for that as it was not giving expected result that's why I thought of using a key column in filter function along with All