DAX to filter values greater than 0 not working

[1125NEWBIUSER]

Evening,

I am writting a DAX code to create a field that will mimic what I am doing with filters in a table. I have the DAX below and based on what I know, not sure why it is not working. I am getting a much different value. Below is also the current table with filters and the new field created with value from the DAX formula. Any advice to what I am doing wrong? Would appreciate any help. Thanks, Chris

[tamerj1]

Hi @1125NEWBIUSER 
Please try

Retail HI Undelivered =
SUMX (
    FILTER (
        'Sycle All Invoice',
        'Sycle All Invoice'[Net Price] > 0
            && 'Sycle All Invoice'[Sales Type] = "Hearing Aid"
            && 'Sycle All Invoice'[Market] = "Retail"
            && 'Sycle All Invoice'[Date Delivered] = BLANK ()
    ),
    'Sycle All Invoice'[Net Price]
)

[1125NEWBIUSER]

I adjusted to this formula and still am can not tie to the manual table with filters. Below are new results.

Thanks, Chris

[tamerj1]

Hi @1125NEWBIUSER 

are you sure the number in the table is correct?

[1125NEWBIUSER]

Hi,

Yes, I am sure the data or total is correct. I was able to manually filter to this in excel with the raw data. I also did this same formula without the >0 part of the filter and adjusted my table without this and was able to get this data to tie out. I am just not able to get the filter to only show the no delivery date or undelivered totals I am looking to achieve. Below is without the >0 filter in and you can see the formula works for this. Something about the filtering on the net price >0 is causing my problem in the Dax.

[v-xiaotang]

Hi @1125NEWBIUSER 

Thanks for reaching out to us.

I cannot reproduce this problem, could you share a sample .pbix file? thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[1125NEWBIUSER]

Hi @v-xiaotang ,

I figured out why the DAX was not working but cannot figure out how to code around it. The DAX is actually working and removing every line with a negative value, the problem is i need it to remove negative values at a subtotal level, when the invoice level totals negative, not each line. Example, visual below, when someone purchases, then cancels, it is the same invoice line but two transactions. One for purchase, one for cancel to remove value. In total, this is zero but with DAX, it removes the transaction level of just cancel making the value actually jump up when i remove negative DAX. Below is an imagine of what I see with data at detail of raw data level. Thanks, Chris

[ReneMoawad]

Hi Chris,

I suggest you create a new column instead of the measure since all the filters used are from the same table

Retail HI Undelivered = 
IF(
    'Sycle All Invoice'[Net Price] > 0

    && 'Sycle All Invoice'[Sales Type] = "Hearing Aid"

    && 'Sycle All Invoice'[Market] = "Retail"

    && 'Sycle All Invoice'[Date Delivered] = BLANK()

    , 'Sycle All Invoice'[Net Price]

    , 0

)

after that you can do the sum of this new column

[1125NEWBIUSER]

Hello,

I created the column using the DAX above and put into table and have this next to the table with field filters. It is coming up with different values still. I am not sure why this is not working but getting similar results to what I had with the calculated field Dax. Below are the results. Thanks again, Chris