Cross Section between Two Groups

[MSW]

Hello I am trying to assess some survey data and struggling to figure out how to find cross sections between varying groups. 

I have categories for each as defined by the questions and aggregates for each of those. I then broke them into scored levels IE expert, advanced... using this measure: Calculate(countrows(values('Table'[Response ID])),FIlter(All('Table'),'Table[score] = "Advanced". 

I did this for the other categories as well. What I am trying to find out is how can I make a measure or column that will give me the cross section between those that rated Advance in one category score with a score of say neutral from another category. 

The end result would look something like: 

Advanced (90 - as counted per ID) and Neutral (60 - counted per ID for that category) and there are X number of IDs that apply to both conditions. 

Appreciate any help provided. 

[v-zhangti · Accepted solution]

Hi, @MSW 

You can try the following methods.
Sample data:

Measure =
CALCULATE (
    COUNT ( 'Table'[Response ID] ),
    ALLEXCEPT ( 'Table', 'Table'[score] )
)

Is this the result you expect? If not, provide sample data and the output you expect.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @MSW 

try to write a measure like this:

AdvancedNeutralCount =

VAR _list1 = 

CALCULATETABLE(

     VALUES(TableName[ResponseID]),

     TableName[Score]="Advanced")

)

VAR _list2 = 

CALCULATETABLE(

     VALUES(TableName[ResponseID]),

     TableName[Score]="Neutral")

)

RETURN

COUNTROWS(INTERSECT(_list1, _list2))