User Profile
North_Man
Frequent Visitor
Joined 2 years ago
User Widgets
Contributions
Re: Any alternative to using Filter for performance
I will mark this as the solution if anything better comes up will change .Thank you Daniel29195 for your help. MANAGER= VAR TABLE_VAR = CALCULATETABLE ( VALUES ( DIMENSION_TABLE ), DIMENSION_TABLE[DESCRIPTION] = "MANAGER" ) VAR MAX_VAR = MAXX ( FILTER ( TABLE_VAR, FACT_TABLE[DATE_OF_SALE] >= [STARTDATE_] && FACT_TABLE[DATE_OF_SALE] <= [ENDDATE_] && FACT_TABLE [EMPLOYEEID_] = [EMPLOYEE_ID] ), [Value] ) RETURN MAX_VAR2.7KViews0likes2CommentsRe: Any alternative to using Filter for performance
Hi Daniel I came up with the below code using your suggestion which is working much better. If you or anyone else has any ideas to make it even more efficient please let me know. MANAGER= VAR TABLE_VAR = CALCULATETABLE ( VALUES ( DIMENSION_TABLE ), DIMENSION_TABLE[DESCRIPTION] = "MANAGER" ) VAR MAX_VAR = MAXX ( FILTER ( TABLE_VAR, FACT_TABLE[DATE_OF_SALE] >= [STARTDATE_] && FACT_TABLE[DATE_OF_SALE] <= [ENDDATE_] && FACT_TABLE [EMPLOYEEID_] = [EMPLOYEE_ID] ), [Value] ) RETURN MAX_VAR2.8KViews0likes3CommentsRe: Any alternative to using Filter for performance
Maybe I can rephrase disregard my dax ..how would you guys/gals bring in Manager and SM from the dimension table to the fact table satisfying that each sales agent reports to one Manager and one SM at any one time in the most efficient way. Thanks Again2.8KViews0likes0CommentsRe: Any alternative to using Filter for performance
Sorry I see the date shows as number in my Fact Table above . Should look like this : FACT_TABLE DATE_OF_SALE EMPLOYEEID NAME SALES MANAGER SM 1/1/2023 1234 JOHN 100 JOE JIM 1/2/2023 9876 JAMES 125 BOB JIM 1/3/2023 9876 JAMES 200 SABRINA JIM 1/5/2023 7777 ELIZA 250 SABRINA LARRY 1/6/2023 7777 ELIZA 500 SABRINA JIM7.3KViews0likes2CommentsRe: Any alternative to using Filter for performance
Hi Daniel Thank you for reply will try what you proposed when I back in office. Open to here other suggestions as it still has filter which seems to be the issue on performance. As for surrogate key .Don't see how to implement since the agents are making sales daily but roll up to different managers , SM for a certain period of time ..also use for different regions they are responsible for a given period of time. Could not see the screenshot. Thanks7.4KViews0likes3CommentsAny alternative to using Filter for performance
Hi I am wondering if there is an alternative calc to using below to bring in calculated columns from a dimension table. It's affecting performance so wonder how we can drop the FIlter. This is just a snippet I am using the same formula in many places to create my FInal Fact Table. Appreciate any help/thoughts. MANAGER = CALCULATE ( VALUES ( DIMENSION_TABLE[Value] ), FILTER ( ALL ( DIMENSION_TABLE), FACT_TABLE[DATE_OF_SALE] >= DIMENSION_TABLE [STARTDATE_] && FACT_TABLE [DATE_OF_SALE] <= DIMENSION_TABLE[ENDDATE_] && FACT_TABLE [EMPLOYEEID_] = DIMENSION_TABLE [EMPLOYEE_ID] && DIMENSION_TABLE [DESCRIPTION] = "MANAGER" ) ) SM = CALCULATE ( VALUES ( DIMENSION_TABLE[Value] ), FILTER ( ALL ( DIMENSION_TABLE), FACT_TABLE[DATE_OF_SALE] >= DIMENSION_TABLE [STARTDATE_] && FACT_TABLE [DATE_OF_SALE] <= DIMENSION_TABLE[ENDDATE_] && FACT_TABLE [EMPLOYEEID_] = DIMENSION_TABLE [EMPLOYEE_ID] && DIMENSION_TABLE [DESCRIPTION] = "SM" ) ) My tables with what is expected: FACT_TABLE DATE_OF_SALE EMPLOYEEID NAME SALES MANAGER SM 44927 1234 JOHN 100 JOE JIM 44928 9876 JAMES 125 BOB JIM 44929 9876 JAMES 200 SABRINA JIM 44931 7777 ELIZA 250 SABRINA LARRY 44932 7777 ELIZA 500 SABRINA JIM DIMENSION_TABLE EMPLOYEE_ID NAME DESCRIPTION Value STARTDATE_ ENDDATE_ 1234 JOHN MANAGER JOE 1/1/2022 1/1/2099 9876 JAMES MANAGER BOB 1/2/2021 1/2/2023 9876 JAMES MANAGER SABRINA 1/3/2023 1/1/2099 7777 ELIZA MANAGER SABRINA 1/1/2015 1/1/2099 1234 JOHN SM JIM 1/2/2021 1/1/2099 9876 JAMES SM JIM 1/1/2022 1/1/2099 7777 ELIZA SM LARRY 1/1/2015 1/5/2023 7777 ELIZA SM JIM 1/6/2023 1/1/2099 ThanksSolved7.4KViews0likes11Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.