Forum Discussion

TKray23's avatar
TKray23
Regular Visitor
2 years ago
Solved

need help with dax

currently, i have a table with columns: RecordID, Filter1, Filter2, Col_A, Col_B,...

LIST OF UNIQUE RECORDS = IF((TABLE1[FILTER1]=1), TABLE1[FILTER2],0)

it gives me NON UNIQUE list of records because Filter 2 has values 1 and 0.

I need to modify this statement to list only those RECORDIDs where FILTER2=1

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi TKray23 ,

     

    I suggest you to try code as below to create a measure.

    Count =
VAR _SUMMARIZE =
    ADDCOLUMNS (
        'Table',
        "DISCOUNTFILTER2",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Filter 2(1,0)] ),
                ALLEXCEPT ( 'Table', 'Table'[Record id] )
            )
    )
RETURN
    COUNTX ( FILTER ( _SUMMARIZE, [DISCOUNTFILTER2] = 1 ), [Filter 1] )

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • TKray23's avatar
    TKray23
    Regular Visitor
    2 years ago

    Record id

    Filter 1

    Filter2(1,0)

    a

    1

    0

    a

    1

    1

    b

    1

    1

    c

    1

    1

    d

    1

    0

    d

    1

    1

     

    mesure should : if distinct count of recordid =1, than count Filtert1

     

    Count=2

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi TKray23 ,

       

      I suggest you to try code as below to create a measure.

      Count =
VAR _SUMMARIZE =
    ADDCOLUMNS (
        'Table',
        "DISCOUNTFILTER2",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Filter 2(1,0)] ),
                ALLEXCEPT ( 'Table', 'Table'[Record id] )
            )
    )
RETURN
    COUNTX ( FILTER ( _SUMMARIZE, [DISCOUNTFILTER2] = 1 ), [Filter 1] )

      Result is as below.

       

      Best Regards,

      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • TKray23's avatar
      TKray23
      Regular Visitor
      2 years ago

      i NEED TO COUNT ONLY THOSE RECORDS THAT SHOWING UP ONLY ONCE IN FILTER2

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    TKray23 Thanks for the explanation but this is useless.  I hope someone else can assist. 

     

    You don't need to write in capital letters, follow some forum etiquette. Thank you!

    • TKray23's avatar
      TKray23
      Regular Visitor
      2 years ago

      Sorry, haven't realized I had my Caps on, but your deminer is not very friendly either.