Filter out 0 and blanks in summarize dax measure

[Anonymous]

Hi Experts

i have the following measure - i need to filter out where [Alloc in Month] is either blank and 0 (values)

Mymeasure =

VAR mytables = 
Summarize(Llo_data,Llo_data[date,Llo_data[YearMonth],Llo_data[Alloc in month],"Anet Amt",Sum(Llo_data[Net Completed]))
Return

Sumx(mytable,[Anet Amt])

how do a filter to Llo_data[Alloc in month to ignore 0 and blanks

[selimovd · Accepted solution]

Hey @Anonymous ,

you can use the FILTER function to get rid of these rows.

Try the following measure:

Mymeasure =
VAR mytables =
    FILTER(
        SUMMARIZE(
            Llo_data,
            Llo_data[date,Llo_data[YearMonth],
            Llo_data[Alloc in month],
            "Anet Amt", SUM( Llo_data[Net Completed] )
        ),
        Llo_data[Alloc in month] <> BLANK() && Llo_data[Alloc in month] <> 0
    )
RETURN
    SUMX(
        mytable,
        [Anet Amt]
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic