Blank total from measure with HASONEVALUE

vsp_p1 — Tue, 29 Oct 2024 10:51:32 GMT

Hi Experts,

I have a report that shows customers' balances. I want to show the total when only one currency is in the context. I'm trying to achieve this by using the HASONEVALUE() function, but the balance is always blank. What am I doing wrong?

Here is the sample report - https://drive.google.com/file/d/124WPcxeaB9Uu-E34_H9BhkMeE7nbGnaQ/view?usp=sharing

Regards, VSP

Re: Blank total from measure with HASONEVALUE

SamWiseOwl — Tue, 29 Oct 2024 11:14:56 GMT

Hi vsp_p1

Use Distinct Count instead

DISTINCTCOUNT('Fact - Payments'[Currency_Key]),

The customer has multiple copies of the same Currency Key so HasOneValue won't return TRUE

Re: Blank total from measure with HASONEVALUE

Anonymous — Wed, 30 Oct 2024 03:09:18 GMT

Thanks for the reply from SamWiseOwl please allow me to provide another insight:

Hi, vsp_p1

Could you please let us know if SamWiseOwl's response resolved your issue? If it did, kindly accept it as the solution.

Due to our security protocols, I am unable to access the link you provided directly. As a result, I cannot ascertain the root cause of your issue or whether it has been resolved. Could you please provide more context or specific details? This would enable me to assist you more effectively!

When uploading a file, please be careful to delete sensitive information.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Best Regards,

Leroy Lu

Re: Blank total from measure with HASONEVALUE

vsp_p1 — Thu, 31 Oct 2024 09:15:04 GMT

Thank you SamWiseOwl for your response. The solution didn't work for me, but I got some inspiration from it and worked further on my DAX to find a solution.

HASONEVALUE filters down to one distinct value (HASONEVALUE - DAX Guide), so having multiple lines was not the problem. The problem was I was getting 0 for some value, which I conditionally made blank at the end. The DAX was returning lines with different currencies, some with 0 balance (or that's how I understand it). So, I had to first remove those lines with balance = 0.

Anonymous, anyone with the link can access the file, so I'm not sure why you can't 🙂 But my problem is (was) that my DAX made the total BLANK when I used HASONEVALUE on currency. It looked like there was only one currency, but there were more.

Here is the DAX that gave me what I wanted, which I'm using now in the report. If you guys can suggest improvements to it, that would be great. (Please note that the measures used here come from on-prem SSAS cube).

Balance - Invoice = VAR _CurrencyCount = SWITCH ( MAX('Due Filter'[Due]), "All", COUNTX ( FILTER ( SUMMARIZE('Fact - Payment', 'Fact - Payment'[Currency_Key], "Balance", [Invoice Balance]), [Balance] <> 0 ), [Currency_Key] ), "Today", COUNTX ( FILTER ( CALCULATETABLE ( SUMMARIZE('Fact - Payment', 'Fact - Payment'[Currency_Key], "Balance", [Invoice Balance]), FILTER('Date - Due', 'Date - Due'[Due Date] = TODAY()) ), [Balance] <> 0 ), [Currency_Key] ), "In 7 days", COUNTX ( FILTER ( CALCULATETABLE ( SUMMARIZE('Fact - Payment', 'Fact - Payment'[Currency_Key], "Balance", [Invoice Balance]), FILTER('Date - Due', 'Date - Due'[Due Date] >= TODAY() && 'Date - Due'[Due Date] <= DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + 7)) ), [Balance] <> 0 ), [Currency_Key] ), "In 14 days", COUNTX ( FILTER ( CALCULATETABLE ( SUMMARIZE('Fact - Payment', 'Fact - Payment'[Currency_Key], "Balance", [Invoice Balance]), FILTER('Date - Due', 'Date - Due'[Due Date] >= TODAY() && 'Date - Due'[Due Date] <= DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + 14)) ), [Balance] <> 0 ), [Currency_Key] ), "In 30 days", COUNTX ( FILTER ( CALCULATETABLE ( SUMMARIZE('Fact - Payment', 'Fact - Payment'[Currency_Key], "Balance", [Invoice Balance]), FILTER('Date - Due', 'Date - Due'[Due Date] >= TODAY() && 'Date - Due'[Due Date] <= DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + 30)) ), [Balance] <> 0 ), [Currency_Key] ), "Overdue", COUNTX ( FILTER ( CALCULATETABLE ( SUMMARIZE('Fact - Payment', 'Fact - Payment'[Currency_Key], "Balance", [Invoice Balance]), FILTER('Date - Due', 'Date - Due'[Due Date] < TODAY()) ), [Balance] <> 0 ), [Currency_Key] ) ) VAR _InvBal = SWITCH ( MAX('Due Filter'[Due]), "All" , [Invoice Balance], "Today" , CALCULATE([Invoice Balance], FILTER('Date - Due', 'Date - Due'[Due Date] = TODAY())), "In 7 days" , CALCULATE([Invoice Balance], FILTER('Date - Due', 'Date - Due'[Due Date] >= TODAY() && 'Date - Due'[Due Date] <= DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + 7))), "In 14 days", CALCULATE([Invoice Balance], FILTER('Date - Due', 'Date - Due'[Due Date] >= TODAY() && 'Date - Due'[Due Date] <= DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + 14))), "In 30 days", CALCULATE([Invoice Balance], FILTER('Date - Due', 'Date - Due'[Due Date] >= TODAY() && 'Date - Due'[Due Date] <= DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + 30))), "Overdue" , CALCULATE([Invoice Balance], FILTER('Date - Due', 'Date - Due'[Due Date] < TODAY())) ) RETURN IF ( HASONEVALUE('Fact - Payment'[Currency_Key]), IF(_InvBal = 0, BLANK(), _InvBal), IF ( _CurrencyCount = 1, IF(_InvBal = 0, BLANK(), _InvBal), BLANK() ) )

Thank you again.

Regards, VSP

topic Re: Blank total from measure with HASONEVALUE in DAX Commands and Tips

Blank total from measure with HASONEVALUE

Re: Blank total from measure with HASONEVALUE

Re: Blank total from measure with HASONEVALUE

Re: Blank total from measure with HASONEVALUE