Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GunnerJ
Post Patron
Post Patron

Best way to combine two measures (if possible)

Below are two measures that show active acounts when compared to a date filter. Sadly I needed to create the 2nd one "Total Actives" since the flag measure wasn't giving distinct values. The 2nd measure references the value in the first and gives the desired results but having two measures to get one result is hurting performance. If it was possible to merge these into one measure I beleive performance metrics could improve. Any help would be appreciated. Feel free to ask any quesitons and I'll try my best to answer. 

Total Actives Flag = 
VAR vMAXDATE = MAX('Charges Date Table'[Date]) 
VAR __Table0 = 
    CALCULATETABLE(
        GENERATE( 
            VALUES( 'MASTER CHARGE ACTIONS'[BI_ACCT]),
            SELECTCOLUMNS(
                TOPN(1, FILTER( 'MASTER CHARGE ACTIONS', 'MASTER CHARGE ACTIONS'[CHG_DATE]<=vMAXDATE),
                    'MASTER CHARGE ACTIONS'[ROWRANK]
                ),
                "CHG_DATE", 'MASTER CHARGE ACTIONS'[CHG_DATE],
                "Account Status", 'MASTER CHARGE ACTIONS'[Account Status]
            )
        ),
        ALLEXCEPT('MASTER CHARGE ACTIONS','MASTER CHARGE ACTIONS'[BI_ACCT],'MASTER CHARGE ACTIONS'[Zone])
    )

var result = COUNTROWS( FILTER( __Table0, [Account Status] = "Active" ) )

return result

 

Total Actives = 

VAR __Table0 = 
    CALCULATETABLE(
        GENERATE( 
            VALUES( 'MASTER CHARGE ACTIONS'[BI_ACCT]),
            SELECTCOLUMNS(
                TOPN(1, FILTER( 'MASTER CHARGE ACTIONS', [Total Actives Flag] >= 1),
                    'MASTER CHARGE ACTIONS'[ROWRANK]
                ),
                "CHG_DATE", 'MASTER CHARGE ACTIONS'[CHG_DATE],
                "Account Status", 'MASTER CHARGE ACTIONS'[Account Status]
            )
        ),
        ALL('MASTER CHARGE ACTIONS'[BI_ACCT],'MASTER CHARGE ACTIONS'[Zone])
    )

var result = COUNTROWS( FILTER( __Table0, 'MASTER CHARGE ACTIONS'[Total Actives Flag] <> BLANK()) )

return result 
2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @GunnerJ ,

Sorry, can you share a simple data and the results you want to show? For better testing.

 

please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

 


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

@v-yalanwu-msft 

Both measures are in the "Master Charge Actions" table. I created a test page called "Flag Test Page"

https://www.dropbox.com/s/yh709cckv105pyf/OZARKSGO%202.0.pbix?dl=0

 

I have it filtered to account# 132003 and the date slider to 7/6/2019. With that date you can see the total actives flag measure is activated giving a value of 1. If you move the slider above July 10th it'll blank out since the running total hit 0 and the customer was made inactive. On the right hand side of the screen you can see accounts where the flag is greater than 1 which messes with my total and thus having to create a second measure to get distinct values. Ideally it should never go above 1 if the account is active for the given date. I was hoping if the measures could be combined it'd help with the performance of the dashboards. 

 

Please let me know if you could use more information than what has been provided!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.