powerbi - Count unique matching items with filter as a calculated column

Question

I have two tables are Data and Report.

Data Table:

In Data table contain three columns are Item, status, and filter.

The item contains duplicated entry and the item column contains text and number or number only or text only.

The status column contains two different text/comments, "Okay" and "Not Okay"

The filter column contains two different filters which are A1 and A2.

The report table

In the Report table, I updated both comments/text as "Okay" or "Not Okay". I am looking for count against filter A1 and A2 according to the comments.

I would like to create a new calculated column in the report table in order to get the unique count according to the comments and filter based on the data table columns item and status.

DATA:

REPORT

Alexis Olson helped the following calculated column in order to get the unique count. I am trying to add one more filter in existing DAX calculated column but it's not working. Can you please advise?

1.Desired Result =
VAR Comment = REPORT[COMMENTS]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( DATA[ITEM] ),
        DATA[STATUS] = Comment
    )
    
2.Desired Result =
COUNTROWS (
    SUMMARIZE (
        FILTER ( DATA, DATA[STATUS] = REPORT[COMMENTS] ),
        DATA[ITEM]
    )
)

3.Desired Result =
SUMX (
    DISTINCT ( DATA[ITEM] ),
    IF ( CALCULATE ( SELECTEDVALUE ( DATA[STATUS] ) ) = REPORT[COMMENTS], 1, 0 )
)

question from:https://stackoverflow.com/questions/65846163/count-unique-matching-items-with-filter-as-a-calculated-column

Answer 1

I think you can just add a filter to CALCULATE:

Filter by A1 Result =
VAR Comment = REPORT[COMMENTS]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( DATA[ITEM] ),
        DATA[STATUS] = Comment,
        DATA[FILTER] = "A1"
    )

For the second method,

Filter by A1 Result =
COUNTROWS (
    SUMMARIZE (
        FILTER ( DATA, DATA[STATUS] = REPORT[COMMENTS] && REPORT[FILTER] = "A1" ),
        DATA[ITEM]
    )
)

I do not recommend using the third one but it would be like this

Filter by A1 Result =
SUMX (
    DISTINCT ( DATA[ITEM] ),
    IF (
        CALCULATE ( SELECTEDVALUE ( DATA[STATUS] ) ) = REPORT[COMMENTS]
            && CALCULATE ( SELECTEDVALUE ( DATA[FILTER] ) ) = "A1",
        1,
        0
    )
)