Forum Discussion

aatish178's avatar
aatish178
Helper IV
2 years ago

table visual rows increasing drastically after adding a measure

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

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • aatish178's avatar
    aatish178
    Helper IV
    2 years ago

    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's avatar
      aatish178
      Helper IV
      2 years ago
      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      aatish178
      Helper IV
      2 years ago

      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