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: ID productid desciption A B C 1 1 frozen 1     2 1 frozen 1     3 1 frozen     1 3 2 f...
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.

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.