Forum Discussion

TM_'s avatar
TM_
Frequent Visitor
1 year ago
Solved

Cannot Distinct Count Negative Values

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.
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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:

     

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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_'s avatar
      TM_
      Frequent Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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: