How to return count of occurrences of value within a matrix, but as a measure?

[GaryWalton]

I have the following matrix visual:

Both the column and row headers are determined by field parameters ('View by - A' & 'View by - B'), in this case, Career Band by Region.

The measure 'Hotspot Identified' returns text indicating a hotspot based on a 2 level Z-test (this bit is irrelevant but good for context).

What I would like to show in a card visual is the count of occurrences of the result 'Hotspot' within the matrix table, for the example above, this should be '2'.

I'm not sure if my thinking the following exists:

calculate ( count if ( 'Hotspot Identified' = "Hotspot"), for each intersection of 'View by - A' & 'View by - B')

[GaryWalton]

I have added an idea Here and would appreciate a vote if you feel this would assist you in resolving a similar issue.

[GaryWalton]

@Greg_Deckler Thanks for your assistance, though I can't get this to work to the needs.

There is no common table where (in the example [Career Band] and [Location]) exist to run the summarize on.

I attempted to crossjoin the 'View by - A' & 'View by - B' tables generated as part of them being field parameters, however this returns the list of fields rather than the items within those fields.

To illustrate with tocsv:

VAR __Table =

    ADDCOLUMNS(

      SUMMARIZE( Test,'Test'[View By - A Fields], Test[View By - B Fields]),

      "__Value", [TO Hotspot M/F - Identified Hotspot?]

    )

Additional:

I think my primary issue is not being able to use a variable as a table expression.

In the below:

The field parameter associated value for 'View By - A Fields' returns 'Dim_GCRFData'[CareerBand]

The field parameter associated value for 'View By - B Fields' returns 'Dim_LocationData'[Region]

TBLREF_A & TBLREF_B format the strings to be exactly as I would type in an expression for a table reference i.e. Dim_GCRFData & Dim_LocationData respectively.

I think I'd need to smash the crossjion within the summarize in order to reach the result. 

Var PARAM_A = SELECTEDVALUE('View By - A'[View By - A Fields],0)

Var TBLREF_A = SUBSTITUTE(LEFT(PARAM_A,SEARCH("[",PARAM_A,1)-1),"'","")

Var PARAM_B = SELECTEDVALUE('View By - B'[View By - B Fields],0)

Var TBLREF_B = SUBSTITUTE(LEFT(PARAM_B,SEARCH("[",PARAM_B,1)-1),"'","")

Var TBL_C = CROSSJOIN(SELECTCOLUMNS(TBLREF_A,"A",PARAM_A),SELECTCOLUMNS(TBLREF_B,"B",PARAM_B))

Var TBL_D =

ADDCOLUMNS(

    SUMMARIZE( TBL_C,'TBL_C'[A], 'TBL_C'[B]),

        "__Value", [TO Hotspot M/F - Identified Hotspot?]

    )

VAR __Result = COUNTROWS(FILTER(TBL_C, [__Value] = "Hotspot"))

Return

__Result

So how can I 'treat' the variables or 'coerce' intellisense to use variables as table references?

[Greg_Deckler]

@GaryWalton Try:

Measure = 
  VAR __Table = 
    ADDCOLUMNS(
      SUMMARIZE( 'Table', [Career Band], [Location]),
      "__Value", [Hotspot Identified]
    )
  VAR __Result = COUNTROWS(FILTER(__Table, [__Value] = "Hotspot"))
RETURN
  __Result