DAX Formula measure already using TREAT AS

Hello Support ForumDAX Cancellations measures Question.pptx (134.1 KB) :

I wanted to ask for some help on a DAX formula for "cancellations. I have attached the background for the question.

What is throwing me off is that I am using a CALCULATE statement and a TREAT AS to get to the normal required answer for cancellations before trying to filter the result by division.

Now I’m trying to get this measure to work based on a divisional split ( Div 1 & Div 2). The key to tie the measures for this purpose is a many to one relationship between OPEN ORDERS ( table where cancellations are derived from) and PRODUCTS which has the only key ( NO_) to give these two tables a relationship. The PRODUCTS table also has the field where division is available.

Any input is very much appreciated!

Thank you.

Bill S.

Hi @Whitewater100,

Please share a sample PBIX, there seems to be a lot going on.

Just strip what you don’t need, use @BrianJ 's Masking sensitive data technique and limit the number of records in your fact tables. Supplying a PBIX will allow other members to better assist you AND will get you a response more quickly…

All the best.

Hi Melissa:

The file is posted with the General Ledger Question. Does that help?

Basically it’s two tables, three if we want to include dates. Fact table = Open Orders. Dim Table = Products. They are related.(No-) I want to sum up cancellations (the measure I posted )
and filter by fields in the product table.

Thank you for any input on this!

Bill

Looking at the image it says you are not slicing by dates and dates already has a relationship with Bookings so you don’t need TREATAS on dates. According to me you need to use column from open orders to treat is as a column of bookings.

Hi Antriska:
You are right in some degree. I made a mistake thinking the item# (No_) in open orders and products was available to me. But I see cancellations is determined from the booking file and I have no relationship to division which is a field in products.

I don’t think I can arrive at cancellations by division or even product. Can I?

Thank you for replying.

Best regards,

Bill

But you do have relationship between Bookings > Dates > OpenOrder > Products so using CROSSFILTER you could traverse this relationship and obtain something, for example bookings will filter dates, dates will filter open orders, and open order will filter products.

But as Melissa mentioned having a file would help.

Hello AntrikshSharma:

I appreciate your suggestions. What I’m coming to release is I don’t have an item number in the booking file and that’s where the cancellations are recorrded.

I believe I made a mistake asking the question to soon. I will see in the SQL DBase has that field and maybe ask again if I have no luck.

Thank you and Melissa for responding to me. Have a great weekend!

Best regards,

Bill

Sure, you too have a great weekend :slight_smile:

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!"