cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DKMuller Visitor
Visitor

Dax Distinct count filtering current vs last reported data correctly

Hi 

 

I hope someone can help me move forward on this issue, I have in powerpivot created a measure for previous reported data. The measure I have created show correct grand total, but do not specify correctly previous reported data (difference in bank account between March 2 and March 4) , I think due to pivot filtering (March 4) and current reported data have less row specification than previous reported data. 

 

For March 4 measure Bank Acc Prev Date need to specify 173 Bank accounts, but only specify 155, something is wrong in my measure for Bank Acc Prev Date. 

 

'=CALCULATE(DISTINCTCOUNT('BankBalanceData'[Bank account]); FILTER(ALLEXCEPT(BankBalanceData; BankBalanceData[Bank]; BankAcc); 'BankBalanceData'[Date] = (Max('BankBalanceData'[Prev Date]))))

 

 

Same issue for Bank Account and for Bank Account Balance, issue only explained for Bank Account issue. 
 
 
Bank Acc: (measure)
 
=DISTINCTCOUNT('BankBalanceData'[Bank account])
 
Simple count per pivot filter of Bank accounts.
 
 
Bank Acc Prev Date: (measure)
 
'=CALCULATE(DISTINCTCOUNT('BankBalanceData'[Bank account]); FILTER(ALLEXCEPT('BankBalanceData';'BankBalanceData'[Bank];'BankBalanceData'[Bank account];'BankBalanceData'[Bank account name]; BankAcc; 'BankBalanceData'[EntityGroup];CognosEntity[EntityGroup]); 'BankBalanceData'[Date] = (Max('BankBalanceData'[Prev Date]))))
 
=CALCULATE(DISTINCTCOUNT('BankBalanceData'[Bank account]); FILTER(ALLEXCEPT(BankBalanceData; BankBalanceData[Bank]; BankAcc); 'BankBalanceData'[Date] = (Max('BankBalanceData'[Prev Date]))))
 
Two calculation giving same result, but result does not show correct specifications.
 
Trying to calculate difference between current Bank account reporting and same reported on date before current, where current = pivot filter. 
 
Bank accounts specification (row spec) does not match previous reported Bank accounts, but only current Bank accounts = spec is wrong. Grand total is correct.
 
 
Prev Date: (calculated column)
 
=CALCULATE((sum([Balance DKK])); (FILTER(ALLEXCEPT('BankBalanceData';BankBalanceData[Bank account]);[Date]=EARLIER([Prev Date]))))
 
Bank data is not updated for every date, this calculation support to find last repported data.

 

 


Dax distinct count issue.PNG

 

1 REPLY 1
Microsoft v-yulgu-msft
Microsoft

Re: Dax Distinct count filtering current vs last reported data correctly

Hi @DKMuller,

 

As this problem is related to PowerPivot, you'd better post thread on a corresponding forum to get more professional advice. Besides, to better illustrate your requirement, please post sample data.

 

Regards,

Yuliana Gu

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors