Measure to get number of rows in a table with crossfiltering

[Anonymous]

Hello,

Lets say I have a table called dim_clusters and another called dim_countries.
Clusters have a relationship with countries, and selecting a cluster on a slicer/filter will filter the dim_countries table

I am trying to create a measure, that will tell me how many countries it exists based on a slicer selection.
I used the LEN,calculatetable,filter,values but I can never get the right number...

Either I get the number of all rows or the number of rows that I select directly on dim_country slicer.

It should ignore the filter applied on dim_country, but it should take into consideration filters applied on dim_clusters table.

How can I achieve this?

Thank you!

[TomMartens · Accepted solution]

Hey,

here you will find a little PBIX

Not sure if I understand your requirement correctly, but from my point of view this report look quite good:

I created this DAX statement

No = 
CALCULATE( 
    COUNTROWS(
        'Country'
    )
    ,ALL('Country'[Country])
    ,VALUES('Country'[Cluster])
)

The measure results to 2 due to the fact, that the slicer from the country column reduces the the country table and there are just 2 countries in the same cluster as the filtered country Germany.

Hope this is what you are looking for

Regards

Tom

[Anonymous]

Yes, this solution works :).

Thank you.
Cheers!