topic Re: table visual rows increasing drastically after adding a measure in DAX Commands and Tips

table visual rows increasing drastically after adding a measure

aatish178 — Wed, 10 Jul 2024 15:29:58 GMT

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

Re: table visual rows increasing drastically after adding a measure

aatish178 — Thu, 11 Jul 2024 18:13:30 GMT

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

Re: table visual rows increasing drastically after adding a measure

Anonymous — Thu, 11 Jul 2024 07:44:42 GMT

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.

Re: table visual rows increasing drastically after adding a measure

aatish178 — Thu, 11 Jul 2024 11:56:02 GMT

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

Re: table visual rows increasing drastically after adding a measure

aatish178 — Thu, 11 Jul 2024 18:15:14 GMT

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.
When I am taking about a field, it is coming from different table having one to many relationship with my fact table