Group by table using multiple filter conditions is not working

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:-

Sample Data for All Reports Month Wise Table:-

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.