Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MPR
Advocate I
Advocate I

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

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 )

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

7 REPLIES 7
v-danhe-msft
Employee
Employee

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.

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?

@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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I guess this the output you are looking for

 

bank.PNG



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

Thanks in advance.

 

MPR

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 )

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

It works perfectly now.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.