DAX FILTERING

[ianvalleser21]

Hi, I need help. I have two tables connected in via Power BI relationship. The first table acts as the main one and then the second table contains the survey details for each items. I am trying to count the numbers of the surveys that scored 1 from the column CSAT on the second table but it keeps giving me the count of the surveys from the first main column with the same name.

First Table: 

Name: Main Dashboard

Columns:

End of Week

Survey Type

Surveys

- count of 1 surveys is 33

Second Table:

Name: Survey Data

Columns:

End of Week

Survey Type 

Surveys

- count of 1 surveys is 20

The formula I used is this: 

=CALCULATE(COUNTROWS(FILTER((Survey Data),Survey Data[Surveys]=1)),Survey Data, Main Dashboard)

This keeps giving me a score of 33 instead of 20.

[ianvalleser21]

@v-kongfan-msft The table is connected using a helper table with unique values inside the data modelling tab:

Main Table/Table 1:

Month Name	End of Week	Reporting Date	Product Family
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Windows Consumer
December	12/6/2023	12/1/2023	Unspecified
December	12/6/2023	12/1/2023	Unspecified
December	12/6/2023	12/1/2023	Unspecified
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Windows Consumer
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Windows Consumer
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Windows Consumer
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Unspecified
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Microsoft 365 and Office
December	12/6/2023	12/1/2023	Windows Consumer

Table 2/Survey Table:

Month Name	End of Week	Reporting Date	Product Family	Survey Scores
December	12/9/2023	12/1/2023	Microsoft 365 and Office	5
December	12/9/2023	12/1/2023	Microsoft 365 and Office	5
December	12/9/2023	12/4/2023	Microsoft 365 and Office	1
December	12/9/2023	12/4/2023	Microsoft 365 and Office	1
December	12/9/2023	12/4/2023	Microsoft 365 and Office	5
December	12/9/2023	12/4/2023	Microsoft 365 and Office	5
December	12/9/2023	12/4/2023	Microsoft 365 and Office	5
December	12/9/2023	12/4/2023	Windows Consumer	5
December	12/9/2023	12/4/2023	Microsoft 365 and Office	5
December	12/9/2023	12/4/2023	Microsoft 365 and Office	5
December	12/9/2023	12/5/2023	Microsoft 365 and Office	1
December	12/9/2023	12/5/2023	Microsoft 365 and Office	1
December	12/9/2023	12/6/2023	Windows Consumer	1
December	12/9/2023	12/6/2023	Microsoft 365 and Office	4
December	12/9/2023	12/7/2023	Unspecified	1
December	12/9/2023	12/7/2023	Microsoft 365 and Office	4
December	12/9/2023	12/7/2023	Microsoft 365 and Office	5
December	12/9/2023	12/7/2023	Microsoft 365 and Office	5
December	12/9/2023	12/7/2023	Microsoft 365 and Office	5
December	12/9/2023	12/7/2023	Microsoft 365 and Office	5
December	12/9/2023	12/7/2023	Microsoft 365 and Office	5
December	12/9/2023	12/7/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Unspecified	1
December	12/9/2023	12/8/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Windows Consumer	5
December	12/9/2023	12/8/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Microsoft 365 and Office	5
December	12/9/2023	12/8/2023	Unspecified	5
December	12/9/2023	12/9/2023	Unspecified	1
December	12/9/2023	12/9/2023	Windows Consumer	5
December	12/9/2023	12/9/2023	Microsoft 365 and Office	5
December	12/9/2023	12/9/2023	Microsoft 365 and Office	5
December	12/9/2023	12/9/2023	Microsoft 365 and Office	5
December	12/9/2023	12/9/2023	Windows Consumer	5

Connectors:

Month Name

December

End of Week

12/9/2023

12/16/2023

12/23/2023

12/30/2023

Reporting Date

12/1/2023

12/2/2023

12/3/2023

12/4/2023

12/5/2023

12/6/2023

12/7/2023

12/8/2023

12/9/2023

12/10/2023

Product Family

Microsoft 365 and Office

Windows Consumer

Unspecified

[Anonymous]

Hi @ianvalleser21 ,

Based on your description, it appears that there is a problem with the dax formula that is not returning the expected counts from the second table.
You just need to filter the "Survey Data" table and here is the corrected formula:

SurveyCount =
CALCULATE (
    COUNTROWS ( 'Survey Data' ),
    FILTER ( 'Survey Data', 'Survey Data'[Surveys] = 1 )
)

Best Regards,
Adamk Kong

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

[ianvalleser21]

Thank you for the answer. However, whenever I do that it's giving me a weird set of numbers in the table like this one. 

End of Week	Survey Scoure Count
1/6/2024	14,076
12/30/2023	14,076
12/23/2023	14,076
12/16/2023	14,076
12/9/2023	14,076
12/2/2023	14,076
11/25/2023	14,076
11/18/2023	14,076
11/11/2023	14,076
11/4/2023	14,076

[Anonymous]

Hi @ianvalleser21 ,

In your case, how are the table relationships constructed and can you provide some test data for me to troubleshoot.

Best Regards,
Adamk Kong