Optimize DAX due to weird Issue on two connected filtered measure

JazZ0003 · August 5, 2021, 6:50am

Hi,

Pardon if I cannot share the PBIX file as the data is too large (4M rows) and it’s a raw data (Journal Voucher) of our 3 companies, which I consolidated to create an Income Statement and Balance Sheet. I cannot obscure also the amount as my issue is related to it.

I followed the Financial Reporting guide on how I set up my model and calculations.
https://portal.enterprisedna.co/courses/enrolled/448571

So far, Income Statement is working is fine (IncomeStatement.jpg).

This is my main model. Journal vouchers of 3 companies are merged into this one table and are identified by subsidiary column (PBCC, RDTC, APPSCOR).

PBCC, RDTC, APPSCOR are the companies with their designated main accounts on Balance Sheet Excel Template formatted below.

The issue is on the Balance Sheet part, I have a weird issue with my calculation below. The APPSCOR company is calculating correctly. But on the RDTC I cannot get it right, even though they have the same DAX measures.

Maybe there’s a better way to optimize this code. They are the same, but one doesn’t with the other company.

RDTC - Cash and Cash Equivalents = CALCULATE(
SUMX(ConsolidatedData, ConsolidatedData[Amount]),
FILTER(ConsolidatedData, ConsolidatedData[Main Account] in
VALUES(‘X-BSCashAndCashEquivalents’[RDTC])
))

RDTC - Cash and Cash Equivalents - Cml = CALCULATE(
[RDTC - Cash and Cash Equivalents],
FILTER(ConsolidatedData, ConsolidatedData[Subsidiary] = “RDTC”),
FILTER(ALLSELECTED(Dates[Date]), Dates[Date] <= MAX(Dates[Date])
))

I really wanted to share my PBIX but I can’t due to sensitive data. I can share it to someone who can PM if it really needed to resolve my issue. I just can’t paste it here and accessible to anyone.

Thanks,
Jassfer

EnterpriseDNA · August 9, 2021, 1:20am

Bumping this post for more visibility.

ankit · August 10, 2021, 2:48am

Hi @JazZ0003 - You can send PBIX file to me and I can look into the issue. Can also check video by Brian on how to mask sensitive data in sample PBIX file.

Thanks
Ankit J

JazZ0003 · August 10, 2021, 3:06am

Hi @ankit,

How do I send the file? I can’t find any email or PM on your user profile. Can you provide your email address?

I will post the solutions on this thread once we resolve the issue.

Thanks,
Jassfer

EnterpriseDNA · August 11, 2021, 12:31am

Hello @JazZ0003! Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

JazZ0003 · August 11, 2021, 7:04am

It’s ok. No need for masking.

@ankit,

Hope you can drop me an email so I can send you the file. I’ve been trying different DAX measures still couldn’t get it right.

Email: jplamoste@yahoo.com

Thanks,
Jassfer

Keith · August 11, 2021, 9:31am

The object of the forum to help everyone and let people learn from problem of others. We are not suppose to send to personal email address (against the rules).

This is why if the information is confidential we are suppose mask the data as per the video that @EnterpriseDNA stated above.

Everyone in the forum needs to learn.

thanks

Greg · August 11, 2021, 10:21am

Hi @JazZ0003. Perhaps you can filter your data (or reproduce the issue using sample data) to make your PBIX small enough to share. Once done, you can use the upload (up arrow) button to attach and upload your file.
Greg
_eDNA Forum - Format DAX or PQ

ankit · August 11, 2021, 3:47pm

Hi @JazZ0003 - Agreed with others here. Don’t seems an option is available to send on EDNA forum directly and mail is totally no go. Perhaps can mask sensitive data,create sample file and share across.

Thanks
Ankit J

JazZ0003 · August 12, 2021, 7:24am

Hi @ankit, @Keith & @Greg

I really did try to mask or reduce the data. but the lowest row I could get is 2,542,672. Excel can only handle 1M rows. I could not randomize the values also as I will have no basis if the values are correct.

If you can provide any method how to extract these 2.5M rows so I can provide a sample file.

Thanks,

ankit · August 12, 2021, 4:23pm

Hi @JazZ0003 - Unfortunately wouldn’t be able to help here. Hope someone else may reply.

Thanks
Ankit J

Greg · August 12, 2021, 6:06pm

Hi @JazZ0003. AFAIK, DAX Studio doesn’t have an export limit on the number of rows; you should be able to use it to extract the data from your PBIX to CSV files if that is your goal; not sure how this would help you attach your PBIX. The other option that springs to mind is to develop a sample dataset with a (much) smaller number of records that you can use to develop a sample PBIX that illustrates your issue and that can also be used to create a mock-up of your desired results. Hope it helps.
Greg

JazZ0003 · August 13, 2021, 5:20am

Hi @Greg, @Keith, @ankit,

Finally able to do it via CSV file, successfully exported 3M rows of data. Pardon if it’s taking longer to sort it out.
EDNA-SamplePBIX.rar (34.8 MB)

Below is the reference value that should match.
Company A is working fine. Need to sort out Company B & C.

Keith · August 13, 2021, 8:45am

Hi there,

Your file isn’t a power bi file. I’m unable to open an .rar

thanks
Keith

EnterpriseDNA · August 16, 2021, 12:11am

Hello @JazZ0003, a gentle follow up the PBIX file requested by the experts and users. Thanks!

JazZ0003 · August 16, 2021, 12:33am

Hi @Keith,

I Zip the file, need to extract it… anyway here it its.
RDMG Consolidated FS - EDNA.pbix (20.6 MB)

Thanks,
Jassfer

EnterpriseDNA · August 17, 2021, 11:56pm

Bumping this post for more visibility.

JazZ0003 · August 19, 2021, 5:29am

bumping this post.

Hope someone could help me with this.

ankit · August 22, 2021, 12:33pm

Hi @JazZ0003

I have looked into the PBIX file. Not really sure what the exact issue is as initial description and new file is different.

However, one change/Issue I did find is due to relationship b/w Date Table and ConsolidatedData-EDNA, “Main Account” column gets filtered based on Selected Date. If not intended this way,
Can try by removing relationship b/w Date table and Consolidated one and using below formula instead.

Company A.1 - Cash and Cash Equivalents - Cml = CALCULATE(
        [Company A.1 - Cash and Cash Equivalents],
        FILTER('ConsolidatedData-EDNA', 'ConsolidatedData-EDNA'[Date] <= MAX(Dates[Date])
))

Please check if this solves the issue, else please Reexplain the issue with new Datasets and an example.

Thanks
Ankit J

EnterpriseDNA · August 23, 2021, 2:09am

Thank you for taking time to check @JazZ0003 's problem @ankit

@JazZ0003 did the response provided help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!