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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
pfernandez
New Member

Hasonevalue not working

I have a field name called Dr_Cr_Balance, which only has one value in the Chart of Accounts table. In the following expression I am testing to see if Hasonevalue = "debit" or "credit". If "debit, I want to multiple the TOTALYTD by 1, and if the value in the Chart of Accounts is "credit" then multiply it by -1.  The expression works as long as there is a number in the MTD Actual column. If this is blank, then the YTD Actual has the wrong sign. Anyone have any ideas on how to fix this?

pfernandez_1-1675816095589.png

 

 

 

5 REPLIES 5
pfernandez
New Member

Can anyone help with the formula and the error message in my last message?

amitchandak
Super User
Super User

@pfernandez , It is better to have a measure like

 

Calculate(

calculate(1* [Amount $], filter(ChartofAccount, ChartofAccount[Dr_cr_flag]= "Dr")  +

calculate(-1* [Amount $], filter(ChartofAccount, ChartofAccount[Dr_cr_flag]= "Cr"),  Dateytd(Date[Date]) ) 

 

I do not think you all date , make sure date table is marked as date table and used in visual if needed

 

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi Amitchandak, I tried this and I probably have a mistake somewhere in the formula. Can you take a look and help me fix this?

pfernandez_0-1675822710171.png

 

It looks like i was missing a comma in the second CALCULATE function. I added the comma and now I have this:

YTD Actual = CALCULATE(1 * [Amount $], FILTER(Chart_of_Accounts, Chart_of_Accounts[Dr_Cr_Balance] - "debit") + CALCULATE (-1 * [Amount $], FILTER(Chart_of_Accounts, Chart_of_Accounts[Dr_Cr_Balance] = "credit", DATESYTD('Date'[Date], "12/31"), ALL('Date'))))
I am not getting a message saying "too many arguments were passed to the FILTER function. The maximum argument count for the function is 2."

I am still looking for a solution. Does anyone know how to fix the above DAX statement?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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