Dynamic Grouping measure not working

DAX DAX Calculations
1

Hi Data Family,

I am trying to achieve a similar Account Receivables Showcase Report using my own data. The data I got was pulled as a Dynamics GP smart list ( Which comes from SQL server ). I pulled the fields we have and started adding measures to try and achieve the showcase. My measure ( Receivables per group ) isn’t working like it’s supposed to and my customer field only shows a handful of customers when applied into a matrix ( They exist in the table just don’t show up in the matrix ). I am very confused which part I am messing up in my model because I have resolved all the relationship errors I received to get to this point. There are a few notes I have to include as they might help answer why it’s not working.

  • My model does have a few differences such as : I don’t have Clearing date field , Invoice Number is called Document Number, The invoice value field I’m using is called Current Trx Amount which should be the amount that doesn’t get affected by the aging Buckets. " Current" is GP Dynamics smarlist field that should work with the smartlist buckets. " Original Trx Amount" is the original amount without any applications. I entered my own aging buckets as a Table to try and achieve something similar to the showcase ( I didn’t use the smartlist bucket fields ). The measures are really almost similar to the showcase measures. My buckets should be the way I entered them as I need all buckets.
  • Other Buckets disappear when pulled into a matrix it only shows up to 90 days!
  • Some measures return blank when I try to make a simple table. not an error, just blank. nothing shows up.
  • I believe the only Tables that matter are my Fact aging, invoices, Customers & Date. but I am attaching my way reduced file ( I have over 700k rows in the original file )
    Test.pbix (440.3 KB)
    . Also please keep the filters on the Credit Manager field.

Note that Customer Names breaks when you add document number from the invoices table but it does not break when you add them from the aging fact table! you can actually achieve a matrix by putting the measure and Customer Name ( Aging Fact ), Document Number ( Aging Fact ) but the numbers are wrong.
image

I would appreciate an expert to look into this as why I’m so close yet my report isn’t functioning the way it should.

Thanks guys!

2

Hi @mobeidat,
Thank you very much for posting your query in the forum.
I have been reviewing the pbix that you have shared and I see that in the Customers table, there are not all the Customers Index that exist in the Invoices table. There are only 500 of the 2000 that exist in the Invoices table.
If we leave in the invoices table only those records for which the customer exists, dynamic grouping seems to work.

Invoices = FILTER( Invoices_old, RELATED( Customers[Customer Index] ) )

image
image894×503 37.6 KB

Regards,

Test_JAFP.pbix (441.3 KB)

1 Like
4

Hi @mobeidat,
Try to remove the mark Show items with no data from Customer Name if it’s marked.

image
image1326×568 65.7 KB

For the calculation of the Days Left, the condition must be met:

AND( [Invoice Date] < [Selected Date], [Due Date] > [Selected Date] )

Regards,

Test 2_JAFP.pbix (697.4 KB)

5

Hi @jafernandezpuga

Thank you for your response. Checking off that mark does work. However in my case, There are originally values. They are just not showing up in the rest of the Matrix calculation. Could it be a relationship problem from my non-reduced pbix file I should have 4,041 values customers but in the customer table it is showing only 4,022 customers values like below
image
The Document Number from my non-reduced pbix file in the invoices table contains the correct 4,041 values like below
image