cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## DAX distinct count of many columns and sum it

Hello everyone! i have the following table

 Employment column 1 Employmentcolumn 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

2 ACCEPTED SOLUTIONS
Super User

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 =
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] )``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

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

4 REPLIES 4
Super User

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

New Member

Thank you !

Super User

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 =
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] )``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

New Member

Thank you !

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.