Forum Discussion
Helper IIGroup by table using multiple filter conditions is not working
Thank you for posting your query on fabric community forum.
I can understand the problem that you are facing with DAX measure. The error message is because the column, Subscription_Flag, has multiple values. There is no aggregation DAX function for this. So it gives an error message if filtering is not applied in a proper way before the grouping and summarization.
I tried another way of using a calculated table to pre-filter the data and then creating the measure on this table. Here are the steps I have achieved it:
1.sample data I have taken:
No_Subscription_Report_Hits Table:-
Report path
Month
Report_Hits
Subscription_Flag
Report1
January
5
No subscription
Report2
January
15
No subscription
Report3
February
8
No subscription
Report4
February
20
Subscription
Report5
March
3
No subscription
Report6
March
12
No subscription
Report7
April
7
Subscription
Report8
April
9
No subscription
Report9
May
2
No subscription
Report10
May
11
Subscription
Sample Data for All Reports Month Wise Table:-
Month Name
January
February
March
April
May
2. DAX formula for Calculated Table: -Filtered_Report_Hits =
FILTER(
No_Subscription_Report_Hits,
No_Subscription_Report_Hits[Month] IN DISTINCT('All Reports Month Wise'[Month Name]) &&
No_Subscription_Report_Hits[Subscription_Flag] = "No subscription"
)
3. DAX measure:-LT_10_Summarize_test =
VAR _count_report =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER(
GROUPBY(
Filtered_Report_Hits,
Filtered_Report_Hits[Report path],
"Summary Hits
SUMX(CURRENTGROUP(), Filtered_Report_Hits[Report_Hits]
),
[Summarized Hits] < 10
),
"__report_path",
[Report path]
)
)
)
RETURN
IF(_count_report = BLANK(), 0, _count_report)
The calculated table Filtered_Report_Hits will filter out the subscription reports and only include the relevant data based on the month filter. The measure LT_10_Summarize_test will then count the distinct reports accessed less than 10 times. The below screenshot shows result:If this post has helped you, accept it as the correct solution so other members can find it quickly.
Hope this helps!
Thanks.
Super UserCan you please try this dax?
LT_10_Summarize_test =
VAR _count_report =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER(
GROUPBY(
FILTER(
// Apply the Month filter for reports with hits in the selected period
No_Subscription_Report_Hits,
No_Subscription_Report_Hits[Month] IN DISTINCT('All Reports Month Wise'[Month Name])
),
// Summarize the Report Hits
No_Subscription_Report_Hits[Report path],
"Summarized Hits",
SUMX(CURRENTGROUP(), No_Subscription_Report_Hits[Report_Hits])
),
// Filter reports with less than 10 hits and exclude subscription reports
[Summarized Hits] < 10 && No_Subscription_Report_Hits[Subscription_Flag] = "No subscription"
),
"__report_path",
[Report path]
)
)
)
RETURN
IF(_count_report = BLANK(), 0, _count_report)
Also, Ensure the Subscription_Flag column is correctly populated in the No_Subscription_Report_Hits table, and it holds values like "No subscription" for non-subscription reports.
Verify that the relationship between No_Subscription_Report_Hits and All Reports Month Wise is correct and allows proper filtering across months.
With this approach, you should be able to count the reports accessed fewer than 10 times, while also excluding subscription reports.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
SahityaYerubandHi Poojara_D12 ,
Thank you the response, however the fis isn't working.
And to answer to the checks you mentioned, its yes for all of them.
The DAX itself is throwing an error with this code -
A single value for column 'Subscription_Flag' in table 'No_Subscription_Report_Hits' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
I just can't use the aggregations, it won't make sense.I tried to create a table with both the filters, ideally if i can get that to work the code should work -
CALCULATETABLE(FILTER(No_Subscription_Report_Hits, No_Subscription_Report_Hits[Subscription_Flag] = "No subscription"), No_Subscription_Report_Hits[Month] in {"August"})and
FILTER(No_Subscription_Report_Hits, AND(No_Subscription_Report_Hits[Subscription_Flag] = "No subscription", No_Subscription_Report_Hits[Month] in {"August"}))both these gave me 0 records in the table.
Please let me know if you have any sugessions for this.Thanks,
Sahitya Y