Count unique matching items as a calculated column

Question

I have two tables are Data and Report.

Data Table:

In Data table contain two columns are Item and status.

The item column 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 report table

In the Report table, I updated both comments/text as "Okay" or "Not Okay".

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

In Excel, I am applying the following formula

F2=SUM((FREQUENCY(MATCH(A$2:$A$19&"",$A$1:$A$19&"",0)*($B$2:$B$19=$D3),ROW($A$2:$A$19))>0)+0)-1

in order to get my final result.

I don't want measure solutions.

DATA TABLE:

enter image description here

REPORT TABLE:

enter image description here

EXCEL LOGIC:

enter image description here

Hi, Alexis Olson. Thanks for putting actual headers. Can you please advise when you have a time. — johon

Alexis Olson Alexis Olson · Accepted Answer · 2021-01-18T16:18:56

This is much easier in DAX than in Excel and there are many ways to do it.

Here are some possibilities with different approaches:

Desired Result =
VAR Comment = REPORT[COMMENTS]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( DATA[ITEM] ),
        DATA[STATUS] = Comment
    )

Desired Result =
COUNTROWS (
    SUMMARIZE (
        FILTER ( DATA, DATA[STATUS] = REPORT[COMMENTS] ),
        DATA[ITEM]
    )
)

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

Count unique matching items as a calculated column

1 Answers