Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
Anonymous
Not applicable

CALCULATETABLE with filter applied conditionally

Hi,

 

I want to have a filter that is only applied if there is HASONEVALUE(DimCustomer[Age_Segment1_Code]) to consider all values of the customer segment (this is to ensure that when counting new customers I don't miscount customer who just changed age segment / had a birthday - as opposed to being a genuine new customer)

 

At the moment I have wrapped an IF around two COUNTROWS which differ only by applying ALL(DimCustomer[Age_Segment1_Code]) in the case where there is HASONEVALUE returns TRUE.

IF (
        HASONEVALUE ( DimCustomer[Age_Segment1_Code] ),
        COUNTROWS (
            FILTER (
                VALUES ( FactAccount[Customer_Id] ),
                FactAccount[Customer_Id]
                    IN CALCULATETABLE (
                        VALUES ( FactAccount[Customer_Id] ),
                        FILTER (
                            ALL ( DimDate ),
                            IF (
                                MonthYear,
                                DimDate[Year] = APreviousYear
                                    && DimDate[Month] = APreviousMonth,
                                DimDate[Year] + 1 = CurrentYear
                            )
                        ),
                        ALL ( DimCustomer[Age_Segment1_Code] )
                    )
            )
        ),
        COUNTROWS (
            FILTER (
                VALUES ( FactAccount[Customer_Id] ),
                FactAccount[Customer_Id]
                    IN CALCULATETABLE (
                        VALUES ( FactAccount[Customer_Id] ),
                        FILTER (
                            ALL ( DimDate ),
                            IF (
                                MonthYear,
                                DimDate[Year] = APreviousYear
                                    && DimDate[Month] = APreviousMonth,
                                DimDate[Year] + 1 = CurrentYear
                            )
                        )
                    )
            )
        )
    )

Is there a way of integrating it into the filter itself? (the part in red) :

COUNTROWS (
            FILTER (
                VALUES ( FactAccount[Customer_Id] ),
                FactAccount[Customer_Id]
                    IN CALCULATETABLE (
                        VALUES ( FactAccount[Customer_Id] ),
                        FILTER (
                            ALL ( DimDate ),
                            IF (
                                MonthYear,
                                DimDate[Year] = APreviousYear
                                    && DimDate[Month] = APreviousMonth,
                                DimDate[Year] + 1 = CurrentYear
                            )
                        ),
                        IF (HASONEVALUE ( DimCustomer[Age_Segment1_Code] ),
                            ALL ( DimCustomer[Age_Segment1_Code] ),
                            DimCustomer[Age_Segment1_Code]
                        )
                    )
            )
        )

When I try this option I get the following error: 

Cannot convert value '*' of type Text to type True/False

 

Suggestions welcome!

0 REPLIES 0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.