Blank total from measure with HASONEVALUE

[vsp_p1]

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

[vsp_p1 · Accepted solution]

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

[Anonymous]

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

[SamWiseOwl]

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