Forum Discussion

mohsenvafa's avatar
mohsenvafa
Helper I
10 years ago

DAX Record Count - One to Many

Hi   I am new to Power BI and DAX, I need help to write a query for my report. Can someone help me on this, please.   In my data model I have one to many relationship between two tables, Reviews ...
Need Help
AlexChen's avatar
AlexChen
Microsoft Employee
10 years ago

Hi mohsenvafa,

 

For example, you have 2 table Review/ReviewResponse like below. They connected by Review[id] and ReviewResponse[reviewId].

 

Review:

 

ReviewResponse:

 

To show if the review have response or not, you can use RELATEDTABLE() to filter the responses. Please add a column like below:

 

hasanswer = if(countax(RELATEDTABLE(ReviewResponse), ReviewResponse[reviewId] = Reviews[id]) > 0, TRUE(), FALSE())

 

To show count of response you can use following DAX:

 

countOfResponse = if(countax(RELATEDTABLE(ReviewResponse), ReviewResponse[reviewId] = Reviews[id]) = BLANK(), 0, countax(RELATEDTABLE(ReviewResponse), ReviewResponse[reviewId] = Reviews[id]))

 

This is the result for Review Table:

 

Best Regards

Alex