DAX measure sum of inner join 3 tables

[mardel]

Hello,

In my data model I have a 'questions' fact table (A) with question ID's with a join to a table (B) with unique question ID's primary key. This table B has a join with table C 'Signals'. I have a measure to calculate all the questions from the fact table. But now I need a DAX measure to calculate only the questions who are having a signal, so who are indirect related to the signal table (C) by question ID. In SQL its just an count(*) of inner joins between the three tables. How could i do this count in a DAX measure?

Thanks in advance.  

[mardel]

Thanks for your reply. When I try this I could after 'IN' not choose table C. When I type IN VALUES(table C) than it work but not the right sum. 

[123abc]

I apologize for any confusion. If you are unable to reference Table C directly after 'IN', it means you need to adjust the DAX expression to correctly filter the data based on the relationships in your model. To do this, you can use the following DAX measure:

QuestionsWithSignalCount =
COUNTROWS(
FILTER(
'Fact Table (A)',
CALCULATE(
COUNTROWS('Table C'),
TREATAS(
VALUES('Table B'[PrimaryKey]),
'Table C'[PrimaryKey]
)
) > 0
)
)

This DAX measure calculates the count of questions in 'Fact Table (A)' that have an indirect relationship with 'Table C' through 'Table B'.

Here's how it works:

1. TREATAS function is used to create a virtual relationship between the selected primary key values from 'Table B' and 'Table C'. This allows you to treat 'Table B' as if it directly relates to 'Table C'.

2. CALCULATE(COUNTROWS('Table C')) calculates the number of rows in 'Table C' based on the virtual relationship with 'Table B'.

3. The FILTER function then filters 'Fact Table (A)' to include only rows where the count of related rows in 'Table C' (indirectly through 'Table B') is greater than 0.

4. Finally, COUNTROWS counts the rows in the filtered table, giving you the count of questions with a signal.

Make sure to replace the table and column names with the actual names used in your data model. This measure should provide the correct count of questions with a signal.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[mardel]

Thanks gain. Unfortunate its not giving the right sum. As you can see in the model, B is already directly related to C. A and C are indirected related by B. Between A and C there is a many to many relation. Do you have another suggestion?

[123abc · Accepted solution]

If you have a many-to-many relationship between tables A and C, and you want to count the questions in table A that are indirectly related to table C through table B, you'll need to use the SUMX function along with the FILTER and RELATEDTABLE functions to properly traverse the relationships. Here's how you can do it:

QuestionsWithSignalCount =
SUMX(
FILTER(
'Questions', -- Replace with your actual fact table name
COUNTROWS(
FILTER(
RELATEDTABLE('B'), -- Use the related table for table B
NOT(ISBLANK(RELATED('C'[SignalColumn]))) -- Replace 'SignalColumn' with the actual column name from the 'C' table
)
) > 0
),
1
)

This DAX measure does the following:

1. FILTER('Questions', ...) filters the rows in the 'Questions' table.

2. RELATEDTABLE('B') accesses the related table B based on the many-to-many relationship between A and C.

3. The inner FILTER checks if there are related rows in table B that have a corresponding signal in table C.

4. COUNTROWS(...) counts the rows in table B that meet the condition. If the count is greater than 0, it means there's a signal related to that question indirectly through table B.

5. SUMX sums up the 1s for each question that meets the condition, effectively giving you the count of questions with signals.

Make sure to replace 'Questions' with your actual fact table name, and 'C'[SignalColumn] with the actual column name from the 'C' table that you want to use for this check. This DAX measure should correctly count questions indirectly related to table C through table B in the many-to-many relationship scenario.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc]

In Power BI or any other tool that uses DAX (Data Analysis Expressions), you can create a measure to calculate the count of questions that are indirectly related to the "Signals" table (Table C) through an inner join with the intermediate table (Table B). You can achieve this by using the DAX functions that allow you to follow the relationships between tables. Here's how you can create the DAX measure:

Assuming that your table structure is as follows:

Table A (Questions) with a relationship to Table B (Question IDs). Table B (Question IDs) with a relationship to Table C (Signals).

You can create a DAX measure to count the questions with a signal as follows:

Questions with Signals =
COUNTROWS(
FILTER(
'Table A',
RELATED('Table B'[QuestionID]) IN 'Table C'
)
)

Explanation of the DAX measure:

1. FILTER('Table A', ...) filters the 'Table A' to include only rows that meet the condition specified in the following expression.

2. RELATED('Table B'[QuestionID]) is used to follow the relationship from 'Table A' to 'Table B' to get the related 'QuestionID' from 'Table B'.

3. IN 'Table C' checks if the related 'QuestionID' exists in 'Table C' (Signals).

4. COUNTROWS(...) counts the rows in the filtered 'Table A', which represents the questions with signals.

This measure will give you the count of questions in 'Table A' that are indirectly related to 'Table C' through the 'Table B' relationship. It will give you the count of questions with signals.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[mardel]

Thanks again for your reply. I was thinking about the best solution and I think the second one is for my case better than the last one because I don't want to sum the records twice, only a sum of all the questions related to the signals and only count it one time (also when the same question has two or more signals). Its a bit complicated but for now I am going to accept the second solution!