DAX - Count Rows in 'Date' column where Status column = "Open"

[PaulMac]

Hi All

I found a DAX formula ther on the Power BI Forums [See it here] that allowed by to return 0 instead of BLANK

I tweaked this to be specifications and ended up with something like this:

Actual8week = IF (
    CALCULATE ( SUM ( All_Complaints[Date 8 Wk Due] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( All_Complaints[Date 8 Wk Due] ) )
)

This gave me a count of all my 8 week letters. I then had to use some visual level filters (as I stuggle with FILTER in DAX) to only count if the the Date 8 Wk Due is in TODAY.

This code works and gives me a 0 as expected. However utilising the same DAX code on my 4 week letters column give me a result of 44,501 which is odd as I only 1,400~ rows in the dataset!!??

What am I doing wrong here peeps?

Can anyone can help me write something that counts the numbers of rows in the Date 4 Wk Due if the value in the Status column = OPEN and will return 0 instead of BLANK when no value is returned?

Many thanks in advance.

PaulMac

[v-chuncz-msft · Accepted solution]

@PaulMac,

You may take a look at the following links.

• https://www.sqlbi.com/articles/filter-arguments-in-calculate/
• https://community.powerbi.com/t5/Desktop/Moving-Average/m-p/276884#M123486
• https://community.powerbi.com/t5/Desktop/How-to-show-zeroes-in-a-matrix-0-is-not-working/td-p/325999