Forum Discussion
Helper IIGroup by table using multiple filter conditions is not working
Hi All,
I am working on creating a measure which gives distinct count of reports, which have been accessed less than 10 times in the selected time frame.
I have created the below measure -
LT_10_Summarize_test =
var _count_report = COUNTROWS
( DISTINCT
(SELECTCOLUMNS
( FILTER
(GROUPBY
(FILTER
//filter table to consider only the slicer values & No subscription reports
(No_Subscription_Report_Hits,
//AND(No_Subscription_Report_Hits[Subscription_Flag] = "No subscription",
(No_Subscription_Report_Hits[Month] in DISTINCT('All Reports Month Wise'[Month Name]))),
No_Subscription_Report_Hits[Report path], "Summarized Hits", SUMX(CURRENTGROUP(), No_Subscription_Report_Hits[Report_Hits])),
//removing reports with 10 or more hits
[Summarized Hits] < 10), "__report_path", [Report path])))
return if(_count_report = BLANK(), 0, _count_report)
It works fine, however, I also need to be able to filter out the reports flagged as Subscription. When this logic is added, the counts all become 0.
No_Subscription_Report_Hits - is a summarized table
All Reports Month Wise is joined with No_Subscription_Report_Hits using a custom key column (report+month)
1:M (many on the No_Subscription_Report_Hits side as some reports haven't been accessed at all, hence they have no month)
Thanks in advance for helping out.
Regards,
Sahitya Y
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.
6 Replies
- Poojara_D12
Super User
Can 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
v-ssriganeshCommunity Support
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.
- v-ssriganesh
May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- v-ssriganesh
Hi SahityaYeruband
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you. - v-ssriganesh
Hi SahityaYeruband
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
