Measure counting unique Report Key based on values in another column

[AutoKris]

Hi,

I tried to solve it with DAX Calculate, with FIlters and Counting Rows, however cannot get my head around it, even ChatGPT 3.5 nor Bing Chat were able so simply solve this. I think this is easy but cannot get a straight and easy solution.

I have this example table.

Report Key	Index	Comment
A	1
A	2
B	3
B	4	R1
B	5	R1
C	6
C	7	Single
C	8	Multiple
C	9
C	10
D	11	R1
D	12
D	13	Single
D	14
D	15
E	16	R1
E	17
E	18	Multiple
E	19

I want to count the number of unique Report Key that have only Comments that are blank or blank and with value R1, meaning the result here should be 2, as only A and B have this combination of lines that have Blank or R1 values in column Comment.

C has Blanks but also Single and Multiple values in Comment column so it should not be counted. D has Blank and R1 however also Single, so it should not be counted. D has Blank but also Single, so not counted, nor E as it has Blank, R1 but also Multiple, so not counted.

What should be the Measure formula to obtain this result?

Please help! 🙂 Thank you

[Greg_Deckler · Accepted solution]

@AutoKris Try this. PBIX is attached below signature. I guess my job is safe from AI for now...

Measure = 
    VAR __Good = { "R1" }
    VAR __Table = FILTER( 'Table', [Comment] <> BLANK() )
    VAR __Bad = DISTINCT(SELECTCOLUMNS(FILTER( __Table, NOT( [Comment] IN __Good ) ), "__Comment", [Comment] ) )
    VAR __BadKeys = SELECTCOLUMNS( FILTER( 'Table', [Comment] IN __Bad), "__ReportKey", [Report Key] )
    VAR __GoodKeys = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', NOT( [Report Key] IN __BadKeys ) ), "__Key", [Report Key] ) )
    VAR __Result = COUNTROWS( __GoodKeys )
RETURN
    __Result

[AutoKris]

Hi @Greg_Deckler , thanks for the quick reply! Your measure actually worked, by the way, mine below also worked without complex variables 😉 

The calculation logic in the measure is as follows:

• First, calculate the distinct count of Report Keys in the Dump table.
• Filter the Dump table to only include rows where the following conditions are satisfied:
  • The Report Key matches the current row's Report Key (using EARLIER function). This is done by using two nested FILTER functions.
  • The Comments are either blank or "Reason 1".
  • There are no other non-blank comments for that Report Key. This is done by comparing the count of all comments with the count of blank and "Reason 1" comments for that Report Key. If they are equal, then there are no other non-blank comments for that Report Key.

If all of these conditions are met, the row is included in the filtered table and is counted towards the final distinct count of Report Keys.

ReportKeyOnlyEmptyCommentOrReason1 = 
CALCULATE(
DISTINCTCOUNT(Table[Report Key]),
FILTER(
Table,
COUNTROWS(
FILTER(
Table,
Table[Report Key] = EARLIER(Table[Report Key])
&& (
Table[Comment] = BLANK()
|| Table[Comment] = "R1"
)
)
) = COUNTROWS(FILTER(Table, Table[Report Key] = EARLIER(Table[Report Key])))
)
)

Please let me know if you think this is also correct?

Thanks 

[Greg_Deckler]

@AutoKris We'll have to agree to disagree that variables make DAX complex. I find that they make things easier by allowing a top-down coding style and the ability to easily debug things (especially using TOCSV). Purists would say to use a variable over EARLIER but I'm not one of those. Interesting approach and probably the best you can do in order to cram a CALCULTE in there. Wonder which approach is faster at scale...