Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
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!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |