if condition to make category combinations

[amjedashraf]

Hi All,

I need help to figure out the dax for the below 

I have a table with following fields

Trancation ID  Category Combinations

1                      A             One Category

1                      A             Once Category

2                     A              Two Category

2                     b               Two Category

Bascially i need and If conditon like if(tranaction ID contains disitinct one category, "One Category" and if (trancation contain 2 disitnat category, " Two Category and so on can anyone please help me on this.

[v-eqin-msft · Accepted solution]

Hi @amjedashraf ,

1. Create a measure:

Measure =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Category] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Trancation ID] = MAX ( 'Table'[Trancation ID] )
        )
    )
RETURN
    SWITCH ( _count, 1, "One Category", 2, "Two Category" )

2.Add a column:

Column =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Category] ),
        ALLEXCEPT ( 'Table', 'Table'[Trancation ID] )
    )
RETURN
    SWITCH ( _count, 1, "One Category", 2, "Two Category" )

My final output looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[ryan_mayu]

@amjedashraf 

maybe you can try to create one of below column

Column = CALCULATE(DISTINCTCOUNT('Table (2)'[Category]),ALLEXCEPT('Table (2)','Table (2)'[Transaction ID]))&" Category"

Column 2 = 
VAR num=CALCULATE(DISTINCTCOUNT('Table (2)'[Category]),ALLEXCEPT('Table (2)','Table (2)'[Transaction ID]))
return if (num=1,"One Category", if(num=2,"Two Category"))