Forum Discussion
DAX FILTERING
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.
4 Replies
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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 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
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
