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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
adam_macs
Helper I
Helper I

Subgroup analysis returning TRUE when Blank

Hi all, I have created a column that looks up the Customer Group column and returns true/false if any customer within that group has purchased (even if only one or two purchased). 

 

My formula seems to work fine apart from it returning TRUE when the Group column is Blank (it is definitely blank, I tried Trimming and Cleaning it in Query Editor). This column has to remain blank also. 

 

I would like it to return FALSE if that column is blank. Any ideas?

 

IsGroupCustomer = 
VAR _min =
CALCULATE (
COUNTROWS(FILTER('table', 'table'[Purchase])),
FILTER (
'table',
'table'[Customer Group] = EARLIER('table'[Customer Group])
&& NOT(ISBLANK('table'[Customer Group]))
)
)
RETURN
IF (ISBLANK('table'[Customer Group]), FALSE(), IF ( _min > 0, TRUE(), FALSE()))

 

adam_macs_1-1680174304153.png

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

this shall also work:

IsGroupCustomer = 
VAR _condition1 =
COUNTROWS(
    FILTER(
        TableName,
        TableName[Customer Group] = EARLIER(TableName[Customer Group])
            &&TableName[Purchase]
    )
)>=1
VAR _condition2 =
TableName[Customer Group]<>BLANK()
RETURN
IF(
    _condition1&&_condition2,
    TRUE, FALSE
)

View solution in original post

3 REPLIES 3
FreemanZ
Super User
Super User

this shall also work:

IsGroupCustomer = 
VAR _condition1 =
COUNTROWS(
    FILTER(
        TableName,
        TableName[Customer Group] = EARLIER(TableName[Customer Group])
            &&TableName[Purchase]
    )
)>=1
VAR _condition2 =
TableName[Customer Group]<>BLANK()
RETURN
IF(
    _condition1&&_condition2,
    TRUE, FALSE
)

That worked a treat, thanks very much!

FreemanZ
Super User
Super User

hi @adam_macs 

try like:

IsGroupCustomer = 
VAR _condition1 =
COUNTROWS(
    FILTER(
        TableName,
        TableName[Customer Group] = EARLIER(TableName[Customer Group])
            &&TableName[Purchase]=TRUE
    )
)>=1
VAR _condition2 =
TableName[Customer Group]<>BLANK()
RETURN
IF(
    _condition1&&_condition2,
    TRUE, FALSE
)

 

FreemanZ_0-1680181215713.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors