Forum Discussion

AutoKris's avatar
AutoKris
Frequent Visitor
2 years ago
Solved

Measure counting unique Report Key based on values in another column

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 ...
Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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's avatar
AutoKris
Frequent Visitor
2 years ago

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's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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...