table visual rows increasing drastically after adding a 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
257298      BE           10035        2020-06-05        2021-11-29      2369
257298      BE           10035        2021-11-30        2021-12-12      2370
257298      BE           10035        2021-12-13        2022-05-25      2433
257298      BE           10035        2022-05-26         9999-12-31     2311
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 LatestValidFrom=FILTER(ALLSELECTED('Table2'),Table2[EmpNo]=SELECTEDVALUE(Table2[EmpNo]) && Table2[CompCode]=SELECTEDVALUE(Table2[CompCode])
&& Table2[CompNo]=SELECTEDVALUE(Table2[CompNo]) && Table2[Valid From]<=MaxDate)

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

Measure2:

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

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

Using above logic I had got expected result, but the moment i started adding other columns of Table 2 in table visual , it was taking lots of time to produce result and at the end it was giving error with "Value has exceeded the available resource".

In order to find out the root cause of it I have added Measure 2 in table visual and there the table rows has increased drastically, and Measure 2 was showing result as 1 for all the data, so measure2 was taking all the rows into consideration while doing computation.

I want Measure 2 to only consider the available(filtered) data of table visual in the calculation instead of consdering all the data for computation.

Can someone plz help with this

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

[Anonymous]

Hi,@aatish178 

Regarding the issue you raised, my solution is as follows:

1. Below are the measure I've created for your needs:

Measure = 
var MaxDate=MAX(Table1[Date])
Var LatestValidFrom=FILTER(ALL('Table2'),Table2[EmpNo]=MAX(Table2[EmpNo]) && Table2[CompCode]=MAX(Table2[CompCode])
&& Table2[CompNo]=MAX(Table2[CompNo]) && Table2[Valid From]<=MaxDate)
Return
CALCULATE(MAX(Table2[Valid To]),LatestValidFrom)

2.You can perform performance checks in the following places in your desktop.

Here are the results of the code you provided:

Here are the measured results from the code I provided:

Here's the result of the optimization of the second metric you provided:

Measure 2 = 
VAR To1=[Latest Valid To atest Valid To]
RETURN if(MAX(Table2[Valid To])=To1, 1 ,BLANK())

Of course, you can also use the SUMMARIZE() function to aggregate what you need in advance, thus reducing duplicate budgets.

3.Here is the relevant documentation:

SUMMARIZE function (DAX) - DAX | Microsoft Learn

Optimization guide for Power BI - Power BI | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aatish178]

Hi Leory,

Thanks for your help, however using All function will not help, because if user selects some old dates from Valid From Slicers then Latest version will not get calculate on that selection and table will show blank result, to fix this I have used AllSelected

[aatish178]

I had observed , when I am adding a field from different tables having relationship as one to many with my fact table then the number of rows are increasing. How can I make a change in DAX so that it should consider only relevant value from another table instead of considering all the values

[aatish178]

1. Also just to troubleshoot I have added measure2 "Is_LatestVersion" in table visual and it was showing value 1 for many records of fields from different tables. My expectation was the field should only show a result where Measure2 is 1, but the dax was first checking the whole data of a field and then it was providing a result, that's why the performance is decreased.
2. When I am taking about a field, it is coming from different table having one to many relationship with my fact table