Forum Discussion

mansch's avatar
mansch
New Member
4 years ago
Solved

Filter similarities

Hi all

 

There is a list of 4000 dublicated ABC entries assigned to multiple owners. some have given feedback, others not. These ABC entries should get a status by the owner "open", "reviewed" or "not possible". I would like to map all "open" with "reviewed" items and would like to filter for all ABC entries which occur in both lists. Any idea how to do in DAX to display this in a table ? 

 

thanks all

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI mansch,

    You can try to use the following calculated column formula to check the different row count based on the current 'field 1'', 'field 2' group to setting record flags:

    Flag =
VAR check =
    CALCULATETABLE (
        COUNTROWS ( VALUES ( T[3] ) ),
        FILTER (
            T,
            T[1] = EARLIER ( T[1] )
                && OR ( T[2] = EARLIER ( T[2] ), T[2] = "reviewed" )
        )
    )
RETURN
    IF ( check > 1, "Y", "N" )

    Regards,

    Xiaoxin Sheng

4 Replies

  • mansch's avatar
    mansch
    New Member
    4 years ago

    1    |    2    |    3

    AA | open | 

    BB | open | 

    CC | reviewed | 

    DD | reviewed | XX

     

    AA | reviewed | ZZ

    BB | open

    CC | open

    DD | reviewed | YY

     

    Each row is assigned to a different owner. [the empty row is only there for better visibility]

     

    I am aiming to see 

    1. "reviewed"  with a different value in "3"

      - DD has a different value

    2. all "1" with different value in "3" 

      - CC and DD have different values

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      4 years ago

      mansch 

      not sure why the second result is CC and DD. I think should be AA and CC

      pls try this

      @mansch 

not sure why the second result is CC and DD. I think should be AA and CC

Column 2 = 
VAR check=maxx(FILTER('Table','Table'[Column1]=EARLIER('Table'[Column1])&&'Table'[Column2]<>EARLIER('Table'[Column2])),'Table'[Column2])
return if(check<>"","Yes")

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI mansch,

    You can try to use the following calculated column formula to check the different row count based on the current 'field 1'', 'field 2' group to setting record flags:

    Flag =
VAR check =
    CALCULATETABLE (
        COUNTROWS ( VALUES ( T[3] ) ),
        FILTER (
            T,
            T[1] = EARLIER ( T[1] )
                && OR ( T[2] = EARLIER ( T[2] ), T[2] = "reviewed" )
        )
    )
RETURN
    IF ( check > 1, "Y", "N" )

    Regards,

    Xiaoxin Sheng