Forum Discussion

yellowold5's avatar
yellowold5
Helper I
1 year ago
Solved

distinctcount id depending on values

Hi, I need help to distinctcount id base if it cointains in same category:

IDproductiddesciptionABC
11frozen1  
21frozen1  
31frozen  1
32fresh  1
41frozen  1
42fresh  1
52fresh 1 
Total  212

In the tabel I want to distinctcount the ID base on desciprtion:
If in same id but have in description only cointains frozen then A is 1
If in same id but have in description only cointains fresh then B is 1
If in same id but have in both description cointains both frozen and fresh then c is 1

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi yellowold5 ,

    I think maybe you can try these steps below:

    I create a table as you mentioned.

    Then I think you can create a new table and here is my DAX code.

    NewTable =
ADDCOLUMNS (
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[productid], 'Table'[desciption] ),
    "A",
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[desciption] = "frozen"
            )
                = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
            1,
            BLANK ()
        ),
    "B",
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[desciption] = "fresh"
            )
                = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
            1,
            BLANK ()
        ),
    "C",
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[desciption] = "frozen"
            ) > 0
                && CALCULATE (
                    COUNTROWS ( 'Table' ),
                    ALLEXCEPT ( 'Table', 'Table'[ID] ),
                    'Table'[desciption] = "fresh"
                ) > 0,
            1,
            BLANK ()
        )
)

     Finally you will get what you want.

     

     

     

    Best Regards

    Yilong Zhou

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

3 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    Hello yellowold5 , 

     

    Please try creating this measures for A,B, and C and add it in the table..

     

    Count_A =
    CALCULATE(
    DISTINCTCOUNT(TableName[ID]),
    NOT(CONTAINS(TableName, TableName[productdescription], "fresh")),
    CONTAINS(TableName, TableName[productdescription], "frozen"))

     

    Count_B =
    CALCULATE(
    DISTINCTCOUNT(TableName[ID]),
    NOT(CONTAINS(TableName, TableName[productdescription], "frozen")),
    CONTAINS(TableName, TableName[productdescription], "fresh"))

     

    Count_C =
    CALCULATE(
    DISTINCTCOUNT(TableName[ID]),
    CONTAINS(TableName, TableName[productdescription], "frozen"),
    CONTAINS(TableName, TableName[productdescription], "fresh"))

     

    If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

    • yellowold5's avatar
      yellowold5
      Helper I
      1 year ago

      Hi, got this error:
      A function 'CONTAINS' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi yellowold5 ,

    I think maybe you can try these steps below:

    I create a table as you mentioned.

    Then I think you can create a new table and here is my DAX code.

    NewTable =
ADDCOLUMNS (
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[productid], 'Table'[desciption] ),
    "A",
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[desciption] = "frozen"
            )
                = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
            1,
            BLANK ()
        ),
    "B",
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[desciption] = "fresh"
            )
                = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
            1,
            BLANK ()
        ),
    "C",
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[desciption] = "frozen"
            ) > 0
                && CALCULATE (
                    COUNTROWS ( 'Table' ),
                    ALLEXCEPT ( 'Table', 'Table'[ID] ),
                    'Table'[desciption] = "fresh"
                ) > 0,
            1,
            BLANK ()
        )
)

     Finally you will get what you want.

     

     

     

    Best Regards

    Yilong Zhou

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