Dax measure with drill through

[Paul_1982]

After days or trial and error I am finally giving up of being able to find a solution myself. I hope that one of you can help me.

Situation:

I have a general ledger entry table with a few million rows, and a few related tables like a Date table and a Category table.

PowerBI is great with standard date reporting, but the company I work for has specific needs making it impossible to use the standard. The user can select a few options that are set as parameters:

- Report Year: the year for which the report is being made;

- Report Quarter: the quarter until which the report is being made;

- CompareMethod: for instance for selected parameter Report Quarter = Q2 and Report Year = 2022, the user can choose to compare with previous year with 3 options:

      1. Whole Year * Quarter Factor  : data from 1/1/21 until 12/31/21 * 0,5,

      2. Until_Report_Quarter             : data from 1/1/21 until 6/30/21

      3. Whole_Year                            :  data from 1/1/21 until 12/31/21

The main matrix in the visual is a Main Category and Sub Category in the rows, and then Dax Measures for the columns:

- Report Year: this calculates the amount for the report year based on the selected year = Report Year and the selected calculation method,  and other parameters;

- Report Year - 1: Same as before but with the year - 1.

- Report Year - 2: Same as before but with the year - 2.

- Forecast Report Year: forecast data for year = report year.

- Forecast Report Year + 1: forecast data for year = report year + 1.

- Some columns for % difference and amount differences.

It's great to have different columns for each, because that way I can set the column for current year green, previous year -1 dark red and previous year -2 light red, and budget/forecast as blue. This color scheme I use all over the graphs and tables. PowerBi would allow me to set 2022 to green, but I want that only to be green if the Report Year = 2022. If report year = 2023 then 2022 needs to be dark red.

For the DAX formula for "Report Year - 1" I have the following:

Amount Reportyear - 1 =

VAR Until_Report_Quarter =    //This one is used if the user chooses to compare with data until the chosen Report Quarter

        Calculate(

            Sum(F_Grootboekposten[Bedrag excl. BTW]),     //Bedrag is Amount in Dutch

            KEEPFILTERS(DATESBETWEEN (D_Datum[Datum] ,

                            Date (SELECTEDVALUE('Par_Rapportjaar'[Rapportjaar]) - 1 , 1 , 1) , //Startdate. Rapportjaar is the parameter for
                                                                                                                                         report year selected by the user.

                            Date (SELECTEDVALUE('Par_Rapportjaar'[Rapportjaar]) - 1 , SELECTEDVALUE('Par_Rapportkwartaal'[Kwartaalnr]) * 3,  // Quarter number * 3 = last month of the quarter.

                                  SELECTEDVALUE('Par_Rapportkwartaal'[Laatste dag])))) //Enddate which is looked up in the table.

           , 'D_BronGrootboekposten'[Categorie] = "Realisatie"   //Realisatie as opposed to budget.

        )

VAR Whole_Year =  //This one is used if the user chooses to compare with whole year / 2 for instance.

        Calculate(

            Sum(F_Grootboekposten[Bedrag excl. BTW]),   //this is the ledger amount

            KEEPFILTERS(DATESBETWEEN (D_Datum[Datum] ,

                            Date (SELECTEDVALUE('Par_Rapportjaar'[Rapportjaar]) - 1 , 1 , 1) , //Startdatum

                            Date (SELECTEDVALUE('Par_Rapportjaar'[Rapportjaar]) - 1 , 12 , 31 )))  

           , 'D_BronGrootboekposten'[Categorie] = "Realisatie"                   //Filter op Realisatie

        )

Return

0+

SWITCH(SELECTEDVALUE('Par_CompareMethod'[CompareMethod]) ,

    "Until Report Quarter" ,   

        Until_Report_Quarter ,

    "Whole Year * Quarter Factor" ,  

      Whole_Year * SELECTEDVALUE('Par_Rapportkwartaal'[Factor]) ,

    "Whole_Year" ,

        Whole_Year

    )

The problem

The matrix works but is slow. Dhe drill through page only works half.

I create a drill through page specifically for the measure Report Year - 1. It contains amongst others 3x a matrix.

- 1 with Categories and the measure            ((resulting in totals per Category)

- 1 with Ledger Account and the measure   (resulting in totals per Ledger Account)

- 1 With all the booking details: invoice number, ledger index number, account no, supplier, category, date etc..

What I do to limit the results is filter on the matrix "Amount Reportyear - 1" is not 0. That works for the first 2, but not for the last matrix. If I put in the Amount (not the measure) it shows data of all years because calculate filters are not passed, but if I add the measure to filter through that it says max rows 1.000.000 has been exceeded, even though the result before I added the measure is maybe 50 rows.

The problem is 3-fold:

- My measure is pretty slow, can something be done to optimize it?

- How can I pass the year filter from the measure?

- How come 1.000.000 rows have been exceeded, as before I add the measure there's only 50 rows visible.

[Paul_1982]

Time intelligence is not possible for giving the user the options I described and color code the matrix. 

I think the main problem is that PowerBi says the query goes over 1.000.000 rows. If that wasn't the case I could filter on the measure <>0 and then other years would be filtered out with that.  There must be something very inefficient or wrong in my dax formula. The created matrix goes down to row level in the ledger entry. The measure only needs to calculate for the rows shown which is like 50.

[amitchandak]

@Paul_1982 , Please check if you can use time intelligence for you measure 

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

or based on today

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

combined

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

When you drill through measure will not pass the filter. so the measure need to be there in visual to filter that visual

You can create a calculation group to filter measure during drill throiugh