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 but cannot get a straight and easy solution.

I have this example table.

 

Report KeyIndexComment
A1 
A2 
B3 
B4R1
B5R1
C6 
C7Single
C8Multiple
C9 
C10 
D11R1
D12 
D13Single
D14 
D15 
E16R1
E17 
E18Multiple
E19 

 

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

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

3 Replies

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