Of CALCULATE and filters

[Mirithu]

Hello,

I have two tables - Country and Language - as follows: 

The relationship is as below on Country[CountryID] and Language[CID]

I would like to list the number of countries for each language .

I have a simple measure of the count of countries as follows: 

CountryCnt = COUNT( Country[CountryID] )

But if I add it to a table, this is the result. It shows the same value across all languages:

The desired result I want is this one:

I can use CALCULATE as follows to get the correct value for each language, but then I have to specify the language in the filter:

CountryCnt English =

    CALCULATE (

    [CountryCnt],

    FILTER (

        'Language',

        'Language'[Lang] = "English"

    )

)

But how do I get the desired result for all languages as indicated?

[johnt75]

You can create a measure like

Num countries per language =
CALCULATE (
    COUNTROWS ( Country ),
    CROSSFILTER ( Country[Country ID], Language[CID], BOTH )
)

[HotChilli]

You would not use the Country table at all to get the desired result.

Pull Language from the Language table on to a visual.  Then drag CID on to the visual and change the aggregation to COUNT

[Hariharan_R · Accepted solution]

Hi @Mirithu 

Try the below measure.

Measure =
VAR _X =
SUMMARIZE (
Languages,
Languages[Language],
"Count", CALCULATE ( COUNT ( Country[Country] ) )
)
RETURN
SUMX ( _x, [Count] )

Thanks

Hari

[Mirithu]

Thank you @Hariharan_R . The measure works perfectly.

If you do not mind I would like to probe futher into it.

I noticed that you used SUMMARIZE in the variable. If I take the SUMMARIZE only and put in a table, I get the correct result.
But if I use SUMMARIZECOLUMNS as below, the result is wrong - same number for all rows. Even if I remove the CALCULATE, the result is still wrong.

SUMMARIZECOLUMNS(

    'Language'[Language],

    "Count", CALCULATE( COUNT( Country[Country] ) )

    )

Does SUMMARIZECOLUMNS ignore the current filter like CALCULATE does? 
And how would you get the correct result using SUMMARIZECOLUMNS?