Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Percent of difference with dynamic dates and drillthrough

Hi All

 

Help is needed. 

 

(for some reason all tables i post from excel are shown here from right to left , while they shoud be left to right - probably due to hebrew -english copy paste problems ... please note)

 

I want to show on a visual matrix the difference between number of registrants between all the dates the user selected in the slicer (meaning dynamic date filter) - if I have 3 dates: 1.1.2018 ; 1.5.2018 and 1.10.2018 I want to show the difference between 1: 1.5.2018 to 1.1.2018 and between 1.10.2018 to 1.5.2018.

I successfully managed to do this using the following DAX code: (when MyTable is the data table , Reg includes the number of registrants  and date hold the date. please note that dates can be any date and not a day before or month before etc. ...)

 

 

% registrants =

VAR previousDate =

    CALCULATE (

        MAX ( MyTable[Date] ),

        FILTER ( ALLSELECTED ( MyTable), MyTable[Reg] > 0 &&  MyTable[Date]< MAX ( MyTable[Date] ) )

    )

VAR previousREG =

    CALCULATE (

        SUM ( MyTable[Reg] ),

       FILTER ( ALLSELECTED( MyTable) ,  MyTable[Date]= previousDate )

    )

RETURN

           DIVIDE( SUM ( MyTable[Reg] ) -previousReg,previousReg,0)

 

       

 on the following dataset : 

Number of registrantsSCHOOLFACULTYSECTIONDATE
10X1A1.1.2018
20Y1A1.1.2018
40Z2A1.1.2018
50T3B1.1.2018
32P4B1.1.2018
40U5B1.1.2018
10X1A5.1.2018
20Y1A5.1.2018
10Z2A5.1.2018
10T3B5.1.2018
32P4B5.1.2018
40U5B5.1.2018

 

 

Up until this point all goes well - superb!  I get something like this (I put this table in excel to avoid starting to translate my entire dataset):

 

Number of registrantsdate
0%1.1.2018
-36.50%5.1.2018


However , I want to add to my matrix the ability to drill through / expend to the levels of the following Hierarchy: 
this also work if I enable the user to filter the school , faculty , or section using a slicer

***PROBLEM starts here ***

 

  1. Section 
  2. Faculty 
  3. School

 

it seems the drill through are not influence the ALLSELECTED dax statements. let say no filter were applied  I get the following visual:

 

 result i get % change expected SECTIONdate
0%0%A1.1.2018
0%0%B 
    
-79%-42.90%A5.1.2018
-57%-32.80%B 


calculation made by power bi is actually (40-192)/192 instead of (40-70)/70 , because the VAR previousREG for some reason is not filtered by the drill through/expend I made. 

 

so my question really is  -  can a drill through filter can applied and change this DAX code correctly ? I tried to change the code and remove the ALLSELECTED statement:

 

VAR previousREG = CALCULATE ( SUM ( MyTable[Reg] ),
 FILTER (( MyTable) , MyTable[Date]= previousDate ) 
)

 

 


However I received blanks instead of calculation results for some reason (why is that ?)

 

Thanks all for reviewing and trying to help

Elad

 

 

  • hi, Anonymous

    After my research, you could use ALLSELECTED  Function instead of ALLSELECTED Function in your formula as below:

    % registrants = 
    
    VAR previousDate =
    
        CALCULATE (
    
            MAX ( MyTable[Date] ),
    
            FILTER ( ALLSELECTED ( MyTable), MyTable[Number of registrants] > 0 &&  MyTable[Date]< MAX ( MyTable[Date] ) )
    
        )
    
    VAR previousREG =
    
        CALCULATE (
    
            SUM ( MyTable[Number of registrants] ),
    
           FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) ,  MyTable[Date]= previousDate )
    
        )
    
    RETURN
    
               DIVIDE( SUM ( MyTable[Number of registrants] ) -previousReg,previousReg,0)

    Result:

    CALCULATE (

    SUM ( MyTable[Number of registrants] ),

    FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) , MyTable[Date]= previousDate )

    )

     

    you can also add other conditions in it.

     

    Best Regards,
    Lin

     

     

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support

    hi, Anonymous

    After my research, you could use ALLSELECTED  Function instead of ALLSELECTED Function in your formula as below:

    % registrants = 
    
    VAR previousDate =
    
        CALCULATE (
    
            MAX ( MyTable[Date] ),
    
            FILTER ( ALLSELECTED ( MyTable), MyTable[Number of registrants] > 0 &&  MyTable[Date]< MAX ( MyTable[Date] ) )
    
        )
    
    VAR previousREG =
    
        CALCULATE (
    
            SUM ( MyTable[Number of registrants] ),
    
           FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) ,  MyTable[Date]= previousDate )
    
        )
    
    RETURN
    
               DIVIDE( SUM ( MyTable[Number of registrants] ) -previousReg,previousReg,0)

    Result:

    CALCULATE (

    SUM ( MyTable[Number of registrants] ),

    FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) , MyTable[Date]= previousDate )

    )

     

    you can also add other conditions in it.

     

    Best Regards,
    Lin

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Amazing ! works ! i am struggling with this for a week (3 days of which i tried to understand how to login to the fourm using the new user system) 

       

    • Anonymous's avatar
      Anonymous
      Not applicable

      Another question regarding to this data set -

       

      how can i not display the min date ? since it's all 0 anyway due to not have diffrence of change ?

       

      thanks 

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support

        hi, Anonymous

        I have test a lot for it, I'm afraid it couldn't achieve for Matrix visual for now. The simplest method is filter it by slicer.

        Sorry for the late reply for it.

         

         

        Best Regards,

        Lin