Forum Discussion
Mente73
1 year agoFrequent Visitor
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...
- 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
1 year agoFrequent Visitor
Sevenhills,
Thank you for your help.
Regards
M