Grouping when blank is an option

DAX DAX Calculations
1

Hi
I have a table that contains the column % inc, This column includes BLANK field

I have a grouping table
Targeted: Min 0.1 Max 20
Non-Targeted: Min -1 Max 0.1
New Customer: Min Blank Max Blank

I have the measure:

Active Customers:=CALCULATE( [# Customers], FILTER( VALUES( Customers),

 COUNTROWS( 
        FILTER( 'Target Group',
                Customers[% inc]>='Target Group'[Min]
               &&Customers[% inc]<'Target Group'[Max]))
>0))

How do I adapt this so that it also splits out the records where Blank exists in the % inc column

Currently this is returning
Targeted: 10
Non Targeted: 5
Total: 15

I’d like it to return:
Targeted: 10
Non Targeted: 3
New Customers: 2
Total: 15

Help please

2

@AliB,

It’s always really dicey trying to write DAX without a PBIX file to look at the data, data model and test the measure on, but I’ll give this my best shot. If this doesn’t work, please post the PBIX and I’ll be happy to work through the full solution with you.

Active Customers =

VAR CountBlanks =
    CALCULATE ( COUNTROWS ( FILTER ( 'Customers', Customers[% inc] = BLANK () ) ) )
VAR CalcGroupings =
    CALCULATE (
        [# Customers],
        FILTER (
            VALUES ( Customers ),
            COUNTROWS (
                FILTER (
                    'Target Group',
                    Customers[% inc] >= 'Target Group'[Min]
                        && Customers[% inc] < 'Target Group'[Max]
                )
            ) > 0
        )
    )
VAR Result =
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Target Group'[Grouping] ) = "Targeted", CalcGroupings,
        SELECTEDVALUE ( 'Target Group'[Grouping] ) = "Non-Targeted", CalcGroupings - CountBlanks,
        SELECTEDVALUE ( 'Target Group'[Grouping] ) = "New Customers", CountBlanks,
        -9999
    )
RETURN
    Result

Hope this is helpful.

  • Brian
3

Thanks for posting your question @AliB. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

4

Thanks Brian - just the trick :smile: