Forum Discussion

maverickf17's avatar
maverickf17
Helper I
1 year ago
Solved

DAX || Power BI || Calaculate

Hi All, The below is 1 table: The below is calendar table: What I am trying to acheive? --> I want to create a dynamic dax which would calculate sum of new subsriber based on cond...
General Comment
Need Help
Show and Tell
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    Answers are below, based on the the below code

     

    dax1) 

    month_selection = SELECTEDVALUE('calendar'[month_no],0)
    dax2) 
    calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

     

    1)The first question would be why i am getting calcaulate_new_subs value for all month insite i have not clicked anything in the slicer.
    Ideally 0 should have been supplied according to the dax and blank should be returned in the matrix right

     

    Answer, your code triggers context transition for each month, so you alwasy see the months in the visual. Please change the code according to:

    calculate_new_subs = 
    VAR selection =[month_selection]
    RETURN

    CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = selection ))

    --------------------------------
    2) Second question would be if i am sum I am able to achieve the same reuslt as above.
    What is the diff between using 

    dax1) 

    month_selection = SELECTEDVALUE('calendar'[month_no],0)
    dax2) 
    calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

    and 
    Sum_new_subscriber =SUM(net_subscriber_growth[NewSubscribers])  
    if end result is same only in this scenario?
     
    Answer: see first question, you were triggering context transition so you alwasy see the current month and so CALCULATE is useless with that code (see my version to obtain what you want)

     

     

     -----------------------------------------------------------------------------------------
    3) if i am using all inside calculate filter.

    calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(all(net_subscriber_growth),MONTH(net_subscriber_growth[Month]) = [month_selection]))

    I am getting result as below.

     

     


    Is this the expected behavior of ALL Dax?

     

    Answer: yes you are considering all the table for every month with that code, and you are still triggering context transition so you see all months for every month now
    ----------------------------------------------------------------------------------
    4) The fourth quetsion would be what is the diff between 
    1)CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

    and 

    2) CALCULATE(sum(net_subscriber_growth[NewSubscribers]),MONTH(net_subscriber_growth[Month]) = [month_selection])

     

    Answer

    2) is automatically translated into 

    CALCULATE(
                      sum(net_subscriber_growth[NewSubscribers]),
                      FILTER (
                            ALL (     net_subscriber_growth[Month]) ), 
                           net_subscriber_growth[Month] =     [month_selection]
                      )
    )

     

    so in case 2 you are only applying the condition to the month column, ignoring filters if they exist (like the month selected), in the first case you are applying the condition to each row of the entire table and you are considering the filters applied to that table (example the month)

     

    The 2) case is more efficient
    ---------------------------------------------

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

FBergamaschi's avatar
FBergamaschi
Super User
1 year ago

Answers are below, based on the the below code

 

dax1) 

month_selection = SELECTEDVALUE('calendar'[month_no],0)
dax2) 
calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

 

1)The first question would be why i am getting calcaulate_new_subs value for all month insite i have not clicked anything in the slicer.
Ideally 0 should have been supplied according to the dax and blank should be returned in the matrix right

 

Answer, your code triggers context transition for each month, so you alwasy see the months in the visual. Please change the code according to:

calculate_new_subs = 
VAR selection =[month_selection]
RETURN

CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = selection ))

--------------------------------
2) Second question would be if i am sum I am able to achieve the same reuslt as above.
What is the diff between using 

dax1) 

month_selection = SELECTEDVALUE('calendar'[month_no],0)
dax2) 
calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

and 
Sum_new_subscriber =SUM(net_subscriber_growth[NewSubscribers])  
if end result is same only in this scenario?
 
Answer: see first question, you were triggering context transition so you alwasy see the current month and so CALCULATE is useless with that code (see my version to obtain what you want)

 

 

 -----------------------------------------------------------------------------------------
3) if i am using all inside calculate filter.

calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(all(net_subscriber_growth),MONTH(net_subscriber_growth[Month]) = [month_selection]))

I am getting result as below.

 

 


Is this the expected behavior of ALL Dax?

 

Answer: yes you are considering all the table for every month with that code, and you are still triggering context transition so you see all months for every month now
----------------------------------------------------------------------------------
4) The fourth quetsion would be what is the diff between 
1)CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

and 

2) CALCULATE(sum(net_subscriber_growth[NewSubscribers]),MONTH(net_subscriber_growth[Month]) = [month_selection])

 

Answer

2) is automatically translated into 

CALCULATE(
                  sum(net_subscriber_growth[NewSubscribers]),
                  FILTER (
                        ALL ( net_subscriber_growth[Month]) ), 
                       net_subscriber_growth[Month] = [month_selection]
                  )
)

 

so in case 2 you are only applying the condition to the month column, ignoring filters if they exist (like the month selected), in the first case you are applying the condition to each row of the entire table and you are considering the filters applied to that table (example the month)

 

The 2) case is more efficient
---------------------------------------------

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

  • maverickf17's avatar
    maverickf17
    Helper I
    1 year ago

    Hey FBergamaschi  can we connect please over a gmeet?
    If it is fine?
    Please DM me.