DAX distinct count of many columns and sum it

[Julux12]

Hello everyone! i have the following table 

Employment column 1	Employment column 2	Type of work
E11	E33	A
E22	E22	B
E33	E22	A
E22	E11	A
E11	E33	B

I need to add the results of E11 that have a job of type A in both columns and have the following result

	A	B	sum
E11	2	1	3
E22	2	2	4
E33	2	1	3

i tried many ways of this but i dont have the results of the last table, thanks for every idea that you can tell me. Thank you

[Jihwan_Kim · Accepted solution]

HI,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
VAR _newtable =
    ADDCOLUMNS (
        DISTINCT ( 'Table'[Employment column 1] ),
        "@A",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Table'[Type of work] ) ),
                'Table'[Type of work] = "A"
            )
                + COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Employment column 2] = EARLIER ( 'Table'[Employment column 1] )
                            && 'Table'[Type of work] = "A"
                    )
                ),
        "@B",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Table'[Type of work] ) ),
                'Table'[Type of work] = "B"
            )
                + COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Employment column 2] = EARLIER ( 'Table'[Employment column 1] )
                            && 'Table'[Type of work] = "B"
                    )
                )
    )
RETURN
    ADDCOLUMNS ( _newtable, "@sum", [@A] + [@B] )

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/EFgPc

[Julux12]

Thank you !

[Jihwan_Kim · Accepted solution]

HI,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
VAR _newtable =
    ADDCOLUMNS (
        DISTINCT ( 'Table'[Employment column 1] ),
        "@A",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Table'[Type of work] ) ),
                'Table'[Type of work] = "A"
            )
                + COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Employment column 2] = EARLIER ( 'Table'[Employment column 1] )
                            && 'Table'[Type of work] = "A"
                    )
                ),
        "@B",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Table'[Type of work] ) ),
                'Table'[Type of work] = "B"
            )
                + COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Employment column 2] = EARLIER ( 'Table'[Employment column 1] )
                            && 'Table'[Type of work] = "B"
                    )
                )
    )
RETURN
    ADDCOLUMNS ( _newtable, "@sum", [@A] + [@B] )

[Julux12]

Thank you !