Don't include blanks in COUNTROWS

[hmeltonPSL]

I am trying to get the number of workorders with a Weekoffset =0  (this is the current week)  with the below measure.  I am getting that data but I am also getting all the rows where the weekoffset is blank because the date complete is blank.  I can create a card using filters and adding weekoffset is not blank and get the correct number but when I try to recreate the card as a measure, I can't seem to get rid of the blanks.   I have multiple combos using NOT ISBLANK or BLANK but no luck. Any ideas?  Thank you

WOCompCurrentWeek =

COUNTROWS (

    CALCULATETABLE (

        'workorders',

        FILTER (

            'workorders',

            'workorders'[statusid] = 3

        ),

    FILTER (

        'workorders',

        'Workorders'[DivisionType] = "HelpDesk"

    ),

        FILTER (

            'DatesTable',

            ('DatesTable'[Weekoffset] == 0)

        )

    )

)

[v-tsaipranay]

Hi [User],

Thanks for reaching out to the Microsoft fabric community forum.

You're right the issue is that rows with blank DateComplete values cause Weekoffset to be blank, and those are being included in your count unless explicitly filtered out in the measure.

While using visual-level filters can hide those blanks, DAX measures require that condition to be written directly.

Here's the recommended fix:

WOCompCurrentWeek =
CALCULATE (
    COUNTROWS ( 'workorders' ),
    'workorders'[statusid] = 3,
    'workorders'[DivisionType] = "HelpDesk",
    NOT ISBLANK ( 'DatesTable'[Weekoffset] ),
    'DatesTable'[Weekoffset] = 0
)

This ensures only work orders from the current week are counted, and blanks are excluded.

Thanks to @pbiuseruk and @Irwan  for the suggestions  especially around filtering logic and data modeling practices.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[hmeltonPSL]

Thanks but I have tried this and I am still getting the wrong numbers.  The corect number is always one less than the measure...which I am guessing is the Blanks count.

[pbiuseruk · Accepted solution]

Hello,

Couple notes. If you're using the filter function for the same table, then you can use && or || for multiple conditions. 

Why don't you also say in your condition that complete date can't equal blank (just to test if that works)?

Also, I usually make an additional column for my week offset columns which is of a string type and I have a formula in there which says to rename the 0 to "Current Week". I do this because in the line charts or column charts it comes through quite nicely. Also, for all of the other prior weeks I call it Week -X.

[hmeltonPSL]

So I actually changed by view for the dataset to include only completed workorders and changed the Measure to below and it is now working as expected.  Thanks!

WorkordersCompCurrentWeek = COUNTROWS (
CALCULATETABLE (
'completedworkordersonly',
FILTER (
'completedworkordersonly',
'completedWorkordersonly'[DivisionType] = "HelpDesk"
),
FILTER (
'DatesTable',
('DatesTable'[Weekoffset] = 0)
)
)
)

[hmeltonPSL]

Thanks...I tried added not isblank for datecomplete and it gives me the whole recordset.  I will try your second suggestion.

[Irwan]

hello @hmeltonPSL 

i am not sure how you dataset looks like but do you think you can add another measure for removing blank from your countrow measure.

something like below.

COUNTX(
    FILTER(
        'workorders',
        not ISBLANK('DatesTable'[Weekoffset])
    ),
    [WOCompCurrentWeek]
)

Otherwise please share your sample dataset and expected outcome.

Hope this will help.

Thank you.

[hmeltonPSL]

Okay...I tried that but get an error on the measure.

A single value for column 'weekoffset' in table 'DatesTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single......