Group by table using multiple filter conditions is not working

[SahityaYeruband]

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

[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.

[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 

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 · Accepted solution]

Hi @SahityaYeruband 

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.

[Poojara_D12]

Hi @SahityaYeruband 

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 

[SahityaYeruband]

Hi @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