Forum Discussion

Mente73's avatar
Mente73
Frequent Visitor
1 year ago
Solved

Help with calculations based on slicer selection

Hello everyone, new to Power Bi and forum. I appeciate all your help in advance.   I am creating a matrix and want to include a slicer with week, month, Qtr and 1-2H (done through a prm) In the m...
Need Help
  • sevenhills's avatar
    sevenhills
    1 year ago

    Check this video for similar solution:

    https://www.youtube.com/watch?v=obfYqap4hnQ

     

    Coming back to your .pbix, I quickly fix and it is very slow. You may have to do as per the video or look on internet for better performance.

    Optional measure for debug:

    Measure debug = var _sv = Max( Prm_Date[Prm_Date] )

RETURN _sv & " : " & 
switch ( _sv, 
  "Date", SELECTEDVALUE(DimDate[Date]),
  "Week", SELECTEDVALUE(DimDate[WeekofYear]),
  "Month", SELECTEDVALUE(DimDate[Month]),
  "Quarter", SELECTEDVALUE(DimDate[Quarter]),
  "Half Year", SELECTEDVALUE(DimDate[Half Year])
  
  , "??")

     

    Actives PW = 
 var _sv = Max( Prm_Date[Prm_Date] )
 RETURN  
CALCULATE(DISTINCTCOUNT('Actives New'[CustomerID])
            ,FILTER(ALL(DimDate), DimDate[Year]=SELECTEDVALUE(DimDate[Year]) &&
                        switch ( _sv, 
                        "Date", DimDate[Date] = SELECTEDVALUE(DimDate[Date]),
                        "Week", DimDate[WeekofYear]=(SELECTEDVALUE(DimDate[WeekofYear])-1) ,
                        "Month", DimDate[Month] = SELECTEDVALUE(DimDate[Month]),
                        "Quarter", DimDate[Quarter] = SELECTEDVALUE(DimDate[Quarter]),
                        "Half Year", DimDate[Half Year]= SELECTEDVALUE(DimDate[Half Year]) 
                        , "??") 
            )
)

     

    Retention % = If ( not ISBLANK([Actives PW]) && [Actives PW] <> 0, 
 ([Active]-[FTD's])/[Actives PW]
)

     

    I just added Measure debug and Actives PW for checking. you can remove those in the below visuals:

    sample for half year:

    sample for quarter:

     

    Hope it helps!

sevenhills's avatar
sevenhills
Super User
1 year ago

Share the .pbix file by removing the sensitive data. I agree with Anonymous , it is hard for anyone to help!

 

Theory where you are getting infinity / failing the measue:

 

  • You have parameters and the dynamic measure concept.
    The videos talk the same.
  • Your Active and FTD measures are basic measures. No issues with these!
  • Actives PW and Retention % are based on the parameter/slicer values, which is dynamic in your case.
  • Retention % is good in DAX. Once you fix the Actives PW, then this will work.
  • DimDate[Year]=SELECTEDVALUE(DimDate[Year]) .. this is from your Year Slicer. No issues!
  • The issue is using of your slicer selection in this line. The videos talk these and also has .pbix file. You have to use SWITCH in DAX: DimDate[WeekofYear]=SELECTEDVALUE(DimDate[WeekofYear])-1))
    • Based on the slicer, the columns are switching and based on this, you have to change the DAX behavior. 

 

Hope it helps! 



  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago

    Check this video for similar solution:

    https://www.youtube.com/watch?v=obfYqap4hnQ

     

    Coming back to your .pbix, I quickly fix and it is very slow. You may have to do as per the video or look on internet for better performance.

    Optional measure for debug:

    Measure debug = var _sv = Max( Prm_Date[Prm_Date] )

RETURN _sv & " : " & 
switch ( _sv, 
  "Date", SELECTEDVALUE(DimDate[Date]),
  "Week", SELECTEDVALUE(DimDate[WeekofYear]),
  "Month", SELECTEDVALUE(DimDate[Month]),
  "Quarter", SELECTEDVALUE(DimDate[Quarter]),
  "Half Year", SELECTEDVALUE(DimDate[Half Year])
  
  , "??")

     

    Actives PW = 
 var _sv = Max( Prm_Date[Prm_Date] )
 RETURN  
CALCULATE(DISTINCTCOUNT('Actives New'[CustomerID])
            ,FILTER(ALL(DimDate), DimDate[Year]=SELECTEDVALUE(DimDate[Year]) &&
                        switch ( _sv, 
                        "Date", DimDate[Date] = SELECTEDVALUE(DimDate[Date]),
                        "Week", DimDate[WeekofYear]=(SELECTEDVALUE(DimDate[WeekofYear])-1) ,
                        "Month", DimDate[Month] = SELECTEDVALUE(DimDate[Month]),
                        "Quarter", DimDate[Quarter] = SELECTEDVALUE(DimDate[Quarter]),
                        "Half Year", DimDate[Half Year]= SELECTEDVALUE(DimDate[Half Year]) 
                        , "??") 
            )
)

     

    Retention % = If ( not ISBLANK([Actives PW]) && [Actives PW] <> 0, 
 ([Active]-[FTD's])/[Actives PW]
)

     

    I just added Measure debug and Actives PW for checking. you can remove those in the below visuals:

    sample for half year:

    sample for quarter:

     

    Hope it helps!