cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WinterMist
Responsive Resident
Responsive Resident

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

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.

 

WinterMist_3-1652459862165.png

 

WinterMist_2-1652459787995.png

 

WinterMist_4-1652459957699.png

 

WinterMist_6-1652460008402.png

 

WinterMist_7-1652460046180.png

 

WinterMist_8-1652460079474.png

 

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.

 

WinterMist_1-1652459454785.png

 

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
      10
    )
  ),
  '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...

1 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.

View solution in original post

5 REPLIES 5
WinterMist
Responsive Resident
Responsive Resident

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.)

 

WinterMist_1-1652969920895.png

 

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?

 

WinterMist_2-1652971629995.png

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.

 

WinterMist_3-1652972392229.png

 

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
Responsive Resident
Responsive Resident

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:

 

WinterMist_0-1652709477879.png

 

WinterMist_1-1652709512721.png

 

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:

 

WinterMist_0-1652714395342.png

 

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. 

 

WinterMist_1-1652716582265.png

 

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

 

Thank you again,

Nathan

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
Community Support
Community Support

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]
    )
)

vkkfmsft_0-1652667972249.png

 

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.

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors