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!

Mente73's avatar
Mente73
Frequent Visitor
1 year ago

Watched both and the 2nd is closer to what I am trying to achieve however I got lost on the VAR for the periods as they are different to the one I need.

I appreciate if you can help me to make it work.  I can start from scratch.

 

Thanks

M

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper 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
        Icon for Super User rankSuper 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!