cancel
Showing results for
Did you mean:
Frequent Visitor

## how to calculate total country value count from two column

Team 1       Team2

A                 C

B                   A

C                   B

A                  B

C                  A

A                 D

A                 C

B = 3

C  = 3

D = 1

2 ACCEPTED SOLUTIONS
Super User

@Girish_123  you can use a measure like this

``````Measure =
VAR _0 =
UNION (
SELECTCOLUMNS ( 'Table', "c1", 'Table'[column1] ),
SELECTCOLUMNS ( 'Table', "c1", 'Table'[column2] )
)
RETURN
MAXX (
'Table 2',
"freq",
MAXX (
FILTER (
_0,
"Frequency", COUNTX ( FILTER ( _0, EARLIER ( [c1] ) = [c1] ), [c1] )
),
[c1] = EARLIER ( 'Table 2'[column1] )
),
[Frequency]
)
),
[freq]
)``````

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Super User

Define a new table Teams as follows:

``````Teams =
DISTINCT ( UNION ( VALUES ( Table1[Team 1] ), VALUES ( Table1[Team 2] ) ) )``````

Then set up relationships with both Team 1 and Team 2 columns in the model:

You can count both columns like this:

``````Count =
CALCULATE (
COUNT ( Table1[Team 1] ),
USERELATIONSHIP ( Teams[Team], Table1[Team 1] )
)
+ CALCULATE (
COUNT ( Table1[Team 2] ),
USERELATIONSHIP ( Teams[Team], Table1[Team 2] )
)``````

You can do it without setting up the relationships too:

``````Count =
CALCULATE (
COUNT ( Table1[Team 1] ),
Table1[Team 1] IN VALUES ( Teams[Team] )
)
+ CALCULATE (
COUNT ( Table1[Team 2] ),
Table1[Team 2] IN VALUES ( Teams[Team] )
)``````

2 REPLIES 2
Super User

Define a new table Teams as follows:

``````Teams =
DISTINCT ( UNION ( VALUES ( Table1[Team 1] ), VALUES ( Table1[Team 2] ) ) )``````

Then set up relationships with both Team 1 and Team 2 columns in the model:

You can count both columns like this:

``````Count =
CALCULATE (
COUNT ( Table1[Team 1] ),
USERELATIONSHIP ( Teams[Team], Table1[Team 1] )
)
+ CALCULATE (
COUNT ( Table1[Team 2] ),
USERELATIONSHIP ( Teams[Team], Table1[Team 2] )
)``````

You can do it without setting up the relationships too:

``````Count =
CALCULATE (
COUNT ( Table1[Team 1] ),
Table1[Team 1] IN VALUES ( Teams[Team] )
)
+ CALCULATE (
COUNT ( Table1[Team 2] ),
Table1[Team 2] IN VALUES ( Teams[Team] )
)``````

Super User

@Girish_123  you can use a measure like this

``````Measure =
VAR _0 =
UNION (
SELECTCOLUMNS ( 'Table', "c1", 'Table'[column1] ),
SELECTCOLUMNS ( 'Table', "c1", 'Table'[column2] )
)
RETURN
MAXX (
'Table 2',
"freq",
MAXX (
FILTER (
_0,
"Frequency", COUNTX ( FILTER ( _0, EARLIER ( [c1] ) = [c1] ), [c1] )
),
[c1] = EARLIER ( 'Table 2'[column1] )
),
[Frequency]
)
),
[freq]
)``````

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors