DAX Measure [Accounts Lost] Returns [BLANK] Instead of 1

[WinterMist]

Hello, I am working on Year-Over-Year analysis to calculate Account Retention, using the following measures:

- AC0 PY Sales (Previous Year) is working.

- AC0 CY Sales (Current Year) is working.

- AC1 - Accounts is working.  (This is simply a count of the number of Accounts for each year.)

- AC2 - Accounts Gained is working.

- AC4 - Accounts Retained is working.

PROBLEM: The only issue is that AC3 - Accounts Lost is not working.  For most accounts lost, the measure displays [BLANK] instead of correctly calculating "1".

Measure details are shown here.

In 2017, for Agent = To, there are 7 accounts which should be counted as LOST.  (See first 7 rows in screenshot below.)

- Accounts: DO, RA, JO, BL, NI, VI, LO

- These 7 should be counted towards AC3 - Accounts Lost because they have PY Sales & do not have CY Sales.

- NOTE: Account CA should NOT be counted as LOST because PY (Previous Year) Sales = $0.

Currently, only Account LO is correctly counted as LOST.

The other 6 accounts incorrectly show BLANK (when they should show 1) for Accounts Lost.

(Accounts Lost Definition = PY sales exist AND CY sales do NOT exist)

I am guessing the LO record works because LO actually exists in a database row for 2017 (with no sales data); whereas the others do not exist at all in any 2017 database row.  (The others only exist in the previous year 2016.)

As a result, the measure for AC3 - Accounts Lost incorrectly returns BLANK, instead of 1 for the 6 accounts which do not exist in 2017.

AC3 - Accounts Lost =

CALCULATE(

  SUMX(

    VALUES('D Insured'[RootParentID]),

    IF(

      'All Measures'[AC0 PY Sales] <> 0                --PY SALES DO EXIST

      && ISBLANK('All Measures'[AC0 CY Sales]), --CY SALES DO NOT EXIST

      1, 0

    )

  ),

  'D Insured'[IsRootParent] = 1 --ONLY GET ROOTPARENT ACCOUNTS

)

Any advice would be greatly appreciated.

Thank you,

Nathan

P.S.  The above approach was taken from the following page:

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

[WinterMist]

Hello @v-kkf-msft ,

Incredible!  It works.  Thank you very much for your help!

Unfortunately, the more I use DAX, the less I understand it.

1) I believe that Amit Chandak knows what he is talking about, since he is a leading Super User on PBI Forums.  So why does his measure logic return BLANKS for Accounts Lost on the PROD PBIX?  (This is even more confusing to me because the inverse measure for Accounts Gained actually works.)

2) Why does your original two-measure solution work on the test PBIX you provided, but not on the PROD PBIX?

3) The only difference I see between your 1st & 2nd revisions of the second measure is VALUES vs. ALLSELECTED.  Why does VALUES continue to return BLANKS, while ALLSELECTED properly populates the Lost records with 1?

I am realizing more and more:

- when DAX works, I do not understand why.

- when DAX doesn't work, I do not understand why.

I know that everything has to do with FC & RC, but there must be context happening behind the scenes that I am not aware of.

Thanks again very much for your help!

Regards,

Nathan

P.S.  Here is a screenshot of the working solution.

[v-kkf-msft]

Hi @WinterMist ,

The AllSELECTED function used here changes the context of the iterator function SUMX.

I think this document should help you understand the context.

How to use RANKX in DAX (Part 2 of 3 – Calculated Measures) 

Best Regards,
Winniz

[WinterMist]

Hello @v-kkf-msft ,

Thanks very much for your time.

I downloaded your PBIX and can see that it is working as desired in that specific PBIX.

However, the same 2 measures do not seem to have the same effect in the PROD PBIX.

(Unfortunately, I cannot share the PROD PBIX for data privacy reasons.)

 The following 2 measures have been created:

After creating the 2 measures, I replaced my original AC3 - Accounts Lost with your new AC3B - Accounts Lost.  The result is the same as before.  The same 6 accounts still show [BLANK] for the new measure for AC3B - Accounts Lost.  Screenshot shown here:

As a troubleshooting step, I then pulled Measure 1 into the table visual (AC3A - Account IsLost), and noticed a few interesting things:

1) AC3A - Account IsLost correctly shows "1" instead of [BLANK] for the 6 records in question.  This is good.  It's definitely a step in the right direction.  But adding this column creates significant problems...

2) Adding this column appears to break the slicer connection with both Year & Account.

- Previously, only 2017 records appeared when Slicer Year = 2017.  Now, ALL years appear.

- Previously, only the Accounts related to the selected Year & Agent appeared.  Now, ALL Accounts for ALL Years appear, regardless of slicer selection.

- As a result, the table visual now shows thousands of rows, instead of only the 40 or so records that are relevant to the slicer selection.

3) Strangely, the Totals row at the end of the table works for all columns EXCEPT the new measure AC3A - Account IsLost.  Even though "1" is now appearing in the table visual for the 6 records in question, for some reason, "0" is shown as the total in that column. 

Let me know if there's anything else I should try.

Thank you again,

Nathan

[v-kkf-msft · Accepted solution]

Hi @WinterMist ,

Perhaps you could try the following measures to see if it returns the correct value.

IsLost = 
CALCULATE (
    IF ( [AC0 PY Sales] <> 0 && ISBLANK ( [AC0 CY Sales] ), 1 ),
    'D Insured'[IsRootParent] = 1
)

AC3 - Accounts Lost = 
IF (
    ISFILTERED ( 'D Insured'[RootParentID] ),
    [IsLost],
    SUMX (
        CROSSJOIN ( ALLSELECTED ( 'D Insured'[RootParentID] ), ALLSELECTED ( 'D Year'[Year] ) ),
        [IsLost]
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft]

Hi @WinterMist ,

Please try the following measures.

IsLost = 
CALCULATE (
    IF ( [AC0 PY Sales] <> 0 && ISBLANK ( [AC0 CY Sales] ), 1, 0 ),
    'D Insured'[IsRootParent] = 1
)

AC3 - Accounts Lost = 
IF (
    ISFILTERED ( 'D Insured'[RootParentID] ),
    [IsLost],
    SUMX (
        CROSSJOIN ( VALUES ( 'D Insured'[RootParentID] ), VALUES ( 'D Year'[Year] ) ),
        [IsLost]
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.