Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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] )
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.
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/EFgPc
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/EFgPc
Thank you !
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] )
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.
Thank you !
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |