Cannot Distinct Count Negative Values

[TM_]

Hello.

Unsure what the issue is here - I created a measure to find the difference between two sets of values ('A' and 'B' for simplicity):

Difference =

 IF(ISBLANK(A),

 BLANK(),

 (A- B))

I then made a table of these differences per 'Category' - some of these difference values are, of course, negative. However, when I tried creating a distinct count of these categories where the values were negative, the result comes back as (BLANK):

CategoryOutperformance =

    CALCULATE(

        DISTINCTCOUNT(

        'Test'[Category]

            ),

        FILTER(

            'Test',

                [Difference] < 0

        )

Why is this? And how can I amend to obtain the actual distinct count?

Thank you.

[Bibiano_Geraldo · Accepted solution]

Hi @TM_ ,

You can use the bellow DAX measure to achieve your goal:

CategoryOutperformance = 
CALCULATE(
    DISTINCTCOUNT('Test2'[Category]),
    FILTER(
        ADDCOLUMNS(
            VALUES('Test2'[Category]),
            "Difference",
            VAR A = CALCULATE(SUM('Test2'[Value]), 'Test2'[Subcategory] = "A")
            VAR B = CALCULATE(SUM('Test2'[Value]), 'Test2'[Subcategory] = "B")
            RETURN IF(ISBLANK(A), BLANK(), A - B)
        ),
        [Difference] < 0
    )
)

Your output should look like this:

[rajendraongole1]

Hi @TM_  - It’s possible that there’s an issue with the current context or filter. Let’s try to modify your CategoryOutperformance measure to ensure it’s correctly filtering and counting the distinct categories.

CategoryOutperformance =
CALCULATE(
DISTINCTCOUNT('Test'[Category]),
FILTER(
'Test',
NOT(ISBLANK([Difference])) && [Difference] < 0
)
)

I hope it works, If you’re still having trouble, feel free to share more details or a sample of your data.

[TM_]

Hi @rajendraongole1 

Thank you for your reply! Unfortunately, this hasn't worked.

More detail below, data set: 

I used this formula in full to calculate the Difference where there was a value in Subcategory A: 

Difference =

 VAR A =

 CALCULATE(

 SUM(

    Test2[Value]),

    Test2[Subcategory] = "A"

 )

 VAR B =

 CALCULATE(

 SUM(

    Test2[Value]),

    Test2[Subcategory] = "B"

 )

 RETURN

 IF(ISBLANK(A),

 BLANK(),

 (A - B))

Result works fine it seems; difference is calculated correctly: 

Then as per your reply, I tried this formula to distinct count the difference values which were negative - as above, this should be 6 but instead I get (Blank) on the card visualisation:

CategoryOutperformance =

    CALCULATE(

        DISTINCTCOUNT(

        'Test2'[Category]

            ),

        FILTER(

            'Test2',

                NOT(ISBLANK([Difference])) && [Difference] < 0

        )

    )

Where could I be going wrong?

Thank you. 

[Anonymous]

Hi @TM_ ,

You can create two measures as below to get it, please find the details in the attachment.

Measure = 
VAR _diff = [Difference]
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Test2'[Category] ), FILTER ( 'Test2', _diff < 0 ) )

CategoryOutperformance = SUMX ( VALUES ( Test2[Category] ), [Measure] )

Best Regards