topic Re: Drill down not working for quater in DAX Commands and Tips

Drill down not working for quater

Bikramaditya — Tue, 15 Nov 2022 10:28:22 GMT

Hi All,

I have calaculated lost customeres revenue YOY, but when i drill down to quater it is not matching to the year as below image

here is the dax funtion i am using for Lost customer YOY

Lost Rev YOY =

VAR lastYear = SELECTEDVALUE ( 'Calendar'[Date].[Year] ) - 1

VAR CustomersThisYear = VALUES ( Sales[CUSTOMER_ID] )

RETURN

CALCULATE ( SUM ( Sales[REVENUE] ),

'Calendar'[Date].[Year] = lastYear,

NOT Sales[CUSTOMER_ID] IN CustomersThisYear

)

Re: Drill down not working for quater

daXtreme — Tue, 15 Nov 2022 13:29:35 GMT

// First of all, you should never use // the auto-generated date tables. Never. // There are many good reasons behind this // and I don't have time to explain here. // Please, just don't for your own safety // and sanity. // // Once you've got the proper tables in place, // use this code: Lost Rev YOY = CALCULATE( // This SUM should be made into a basic // measure on its own, like [Total Revenue]. SUM( Sales[REVENUE] ), // 'Calendar' should be marked as Date Table // in the model. DATEADD( 'Calendar'[Date], -1, YEAR), EXCEPT( // By the way, if you want to stay sane // please create a proper star schema // as the model and follow Best Practices // of data modeling in PBI. Otherwise, // you're exposing yourself to issues // you won't even be able to spot. So, // please create a Customer dimension and // use it instead of the field from the // fact table as below. ALL( Sales[Customer_id] ), VALUES( Sales[CUSTOMER_ID] ) ) )

You have to be extremely careful with what you're doing right now. The model is most likely not what it should be and therefore you might get funny results by slicing on fields that belong to the fact table. Honestly, slicing by a fact table's field should never be done in a well designed semantic solution. Please turn your model into a proper one to avoid issues.

Re: Drill down not working for quater

Bikramaditya — Tue, 15 Nov 2022 13:40:19 GMT

Noted,
Thank you for your response

Re: Drill down not working for quater

daXtreme — Tue, 15 Nov 2022 13:42:05 GMT

If the code is OK, please mark the answer as The Solution (there's a button for it). It'll help others as well. Thank you.

Re: Drill down not working for quater

Bikramaditya — Tue, 15 Nov 2022 15:09:23 GMT

It did not work for me, attached the screenshot. Please have look

Re: Drill down not working for quater

daXtreme — Tue, 15 Nov 2022 17:17:27 GMT

Well, if you don't have the model as the code requires... then it'll not work. That's obvious. Also, it does matter which fields from which tables you drop onto the canvas. DAX does not work regardless of models. It does work together with models. That's why it's important that you inform potential helpers about what the model looks like...