Problem with DAX calculating a value using filters from two tables

[JayCee]

First of all, greetings. This is my first post. As a new user my knowledge is limited but I will try to grow in this data world.

In this example, I am trying to calculate against how many 'top' teams (X) has Y (Segmentation choice of another club) faced. I've worked on this with SQL Server because I'm much more familiar with it.

The sentence looks like this:

SELECT COUNT(*)
FROM dimGames
WHERE club_id = 131 AND opponent_id IN (SELECT club_id FROM dimClubs WHERE category = 1)

With SQL I had to specify the 'club_id', but I want this to be the filtered field.

If I change the 'category' to 2, 3, 4... I get the correct number of games 131 (Fc Barcelona) played against 'category X' clubs.

- dimGames has information about specific data of the game, but we only have to know there are 'game_id', 'club_id' and 'opponent_id' columns. Being 'club_id' and 'opponent_id' both related to dimClubs[club_id].

- dimClubs has precise information about a club. In this case, the important columns are 'club_id' and 'category'.

The problem is when I change this sentence to DAX, where 'club_id' and 'opponent_id' share the same 'club_id' column in the dimClubs table.

The farther I've reached is a sentence like this:

Games Vs C1:=
CALCULATE(
COUNTROWS(dimGames),
dimGames[opponent_id] IN VALUES(dimClubs[club_id]), dimClubs[category] = 1)

But there's no result (BLANK) in the Visual Card when I select a club from the Segmentation.

I've tried to add another filter to the CALCULATE:

dimGames[club_id] = SELECTEDVALUE(dimClubs[club_id])

Still getting BLANK.
Sincerely I don't know what to try. Any help?

Thank you so much.

[tamerj1]

Hi @JayCee 
If the return result is blank then the Selected Club do not exist in the Games table (as opponent) or otherise you need to double check the data taype and quality of both columns.

As side note I would say that 2nd filter "dimClubs[category] = 1" probably shall not be effective as both tables are dim tables and most propably there is no direct relationship between the two tables?

[JayCee]

Could it be, Clubs filter Games, but Games (opponent_id) can't filter back to Clubs (club_id)?

The related column is club_id in both, because this dataset I was given has 'two games' for every actual game, one hosting 'Home', the other hosting 'Away'.

I think I should insert kind of a "filter" inside the function, comparing 'opponent_id' with 'club_id' and 'category' = 1... or something like that.

My knowledge of DAX is very limited and I don't know how to even write long sentences in the correct order. 

[tamerj1]

@JayCee 

Yes that will do. However, not sure if that anything to do with blank result. Actually, without the 2nd filter the result should count more rows.