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

DAX Formula Showing Blank

Hello,

 

I have shared a folder with the pbix file and source file.  I hope you can access it.

 

Folder

 

I have a function in DAX that is killing me.  I have given up way too much time and sleep and still can't figure it out. I need you all's help.

 

I have the following function.

 

 

BankAcct1 Balance = 

Var MaxIndex = MAX('Sanitized Transaction Detail v2'[Index])
Var BankAcct1 = "1008 · Chase Checking"
    
Return CALCULATE(
    MIN('Sanitized Transaction Detail v2'[Balance]),
    'Sanitized Transaction Detail v2'[Index] = MaxIndex,
    'Sanitized Transaction Detail v2'[Account] = BankAcct1
)

Following is the source table:

table.jpg

 

 

For some reason, it comes up blank when the last two rows of data are included.  But when they are removed, the function works.  Originally, I had a lot more data but I whittled it down to this and am still have the issue?

 

Does anyone know how I can get the function to not show blank?  The purpose of the function to look at the account stated and find the max index and then provide the balance for the row with the max index.

 

Any help is greatly appreciated.

 

MPR 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX Formula Showing Blank

MaxIndex was not getting calculated correctly,  so we calculate maxindex of the account you are inerested, same I did for other DAX

 

 

 

 

Var BankAcct1 = "1008 · Chase Checking"
Var MaxIndex =
CALCULATE(MAX('Sanitized Transaction Detail v2'[Index]), 
'Sanitized Transaction Detail v2'[Account] = BankAcct1 )

 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

www.perytus.com
(MVP Data Platform)


7 REPLIES 7
Community Support Team
Community Support Team

Re: DAX Formula Showing Blank

Hi @MPR,

Based on my test, you can refer to below steps by using filter function:

1.I have create a Table visual and add the related fields like the picture below:

1.PNG

2.Expand the “BankAcct1 Balance” field in the Filters and choose the “is not blank”, apply the filter.

2.PNG

Now you can see the correct result.

3.PNG

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MPR Frequent Visitor
Frequent Visitor

Re: DAX Formula Showing Blank

Daniel, thanks for the reply.

 

I may be missing how your proposed response will work.  

 

It doesn't seem like filtering out the blanks will work.  My measure for BankAcct1 Balance should already be filtering out the blanks of the balance column which is the column that I am trying to retrieve.  When I filter the table down to the correct Bank Acct (1008 · Chase Checking) and the max index, there should only be one row remaining and the balance column should be non-blank.  

 

If you are able to access the pbix file and excel file I attached, it may help.  What is very strange is if I delete the last two rows in the source table, the BankAcct1 Balance measure start working correctly.  It is no longer blank.  But the BankAcct2 Balance measure is still showing blank.  I'm seriously stumped.

 

Ideas?

Super User
Super User

Re: DAX Formula Showing Blank

@MPRIf I undetstood your requirement correctly, you are looking to get the max balance of each account based on maximum index number of the account, correct?




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

www.perytus.com
(MVP Data Platform)


Super User
Super User

Re: DAX Formula Showing Blank

I guess this the output you are looking for

 

bank.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

www.perytus.com
(MVP Data Platform)


MPR Frequent Visitor
Frequent Visitor

Re: DAX Formula Showing Blank

@parry2kThat is exactly what I need.  How did you do that?  And what was wrong with my measure?

 

Thanks in advance.

 

MPR

Super User
Super User

Re: DAX Formula Showing Blank

MaxIndex was not getting calculated correctly,  so we calculate maxindex of the account you are inerested, same I did for other DAX

 

 

 

 

Var BankAcct1 = "1008 · Chase Checking"
Var MaxIndex =
CALCULATE(MAX('Sanitized Transaction Detail v2'[Index]), 
'Sanitized Transaction Detail v2'[Account] = BankAcct1 )

 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

www.perytus.com
(MVP Data Platform)


Highlighted
MPR Frequent Visitor
Frequent Visitor

Re: DAX Formula Showing Blank

@parry2kPerfect.  Thanks for the eduction.  My maxindex statement wasn't being limited to my bank account.  

 

It works perfectly now.