CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
user3758291
user3758291

Reputation: 129

Filtering by secondary field in DAX

I'm new to DAX.

My model contains a single table called Notices. Notices has 235,969 rows.

Notices has fields CustomerID, NoticeNo and NoticeStatus.

When I set the filter context to 'CANCL' Notices[NoticeStatus], I can see that there are 3 notices that have a CANCL status.

As such, the measure below evaluates to 3 since each of the remaining notices belong to 3 separate customers. However, I would like to base the aggregate on the unfiltered table, but filter out the rows (after aggregating) based on the CustomerIDs that remain in the filter context and [ObCount] = 1. In this case, the measure needs to evaluate to 0 or BLANK(), as none of the CustomerIDs in the filter context remain after filtering for [ObCount] = 1.

Customers with Single Notice Only = 
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS (
            Notices[CustomerID],
            Notices,     
            "ObCount", [All Notices Outstanding]
        ),
        [ObCount] = 1
    )
)

[All Notices Outstanding] = COUNTROWS(Notices)

Upvotes: 0

Views: 39

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You should be able to do this by applying the filtered table as a table filter argument to Notices using CALCULATE:

Customers with Single Notice Only =
CALCULATE (
    COUNTROWS ( Notices ),
    FILTER (
        SUMMARIZECOLUMNS (
            Notices[CustomerID],
            ALL ( Notices ),
            "ObCount", [All Notices Outstanding]
        ),
        [ObCount] = 1
    )
)

Note the use of ALL to remove the filter status filter when summarizing.

Upvotes: 1

Related Questions