distinctcount id depending on values

[yellowold5]

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	fresh			1
4	1	frozen			1
4	2	fresh			1
5	2	fresh		1
Total			2	1	2

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

[Anonymous · Accepted solution]

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]

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]

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.