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!
Anonymous
1 year agoNot applicable
Hi, Mente73
Thanks for sevenhills's reply. You can provide the pbix file without sensitive data for testing, it's hard to reproduce your data with just screenshots. Feel free to help you.
Best Regards,
Yang
Community Support Team