Calculation With Multiple Case Statements Over Multiple Tables - Convert SQL to DAX

[wildrose]

Hello All,

I am new to DAX and struggling with how to acheive a conversion for this SQL. I am unclear how to convert a calculation with multiple Case statements across multiple tables to one in DAX. Any help would be grately appreciated. 

SELECT DISTINCT

sum((

CASE WHEN (

CASE WHEN a.BILLING_LINE_ITEM_CATEGORY_CD IN ('TANN','YBAN','YB1N','RENN','ZGNN','ZLNN')
    and d.MATERIAL_HIERARCHY_LEVEL3_CD NOT IN ('CH000CH090CH090025')
          and e.SO_DOCUMENT_REASON_CD NOT IN ('Z17','Z18','Z19','Z96','Z97','Z98')
     THEN 1 ELSE 0 END

)=1

THEN (b.ADJ_ITEM_COST_LC_AMOUNT) ELSE 0.00 END ) * c.EXCHANGE_RATE)

FROM
SHARED_AREA.BILLING_DOCUMENT_LINE_TYPE_REF a
INNER JOIN SALES_AREA.CUSTOMER_MATERIAL_DAILY_BILLING_FACT b ON (b.BILLING_DOCUMENT_LINE_TYPE_ID=a.BILLING_DOCUMENT_LINE_TYPE_KEY
AND a.BILLING_LINE_ITEM_CATEGORY_CD NOT LIKE '%AE')
INNER JOIN SHARED_AREA.CURRENCY_CONVERSION_REF c ON (c.CURRENCY_CONVERSION_KEY=b.CURRENCY_CONVERSION_ID)
INNER JOIN SHARED_AREA.MATERIAL_DIM d ON (b.MATERIAL_ID=d.MATERIAL_KEY)
INNER JOIN SHARED_AREA.SO_DOCUMENT_LINE_TYPE_REF e ON (b.SO_DOCUMENT_LINE_TYPE_ID=e.SO_DOCUMENT_LINE_TYPE_KEY)

join SHARED_AREA.DATE_DIM dd on dd.date_key = b.BILLING_DATE_ID

where dd.CALENDAR_DATE = '2019-11-15'

[Anonymous]

Hi

The first question is that you must have a model already created as shown below.

table
Dimensions
BILLING_DOCUMENT_LINE_TYPE_REF
    BILLING_DOCUMENT_LINE_TYPE_KEY
    BILLING_LINE_ITEM_CATEGORY_CD

CURRENCY_CONVERSION_REF
    CURRENCY_CONVERSION_KEY
    EXCHANGE_RATE
MATERIAL_DIM
      MATERIAL_KEY
     MATERIAL_HIERARCHY_LEVEL3_CD
DATE_DIM
      date_key
    CALENDAR_DATE

Fact Table
CUSTOMER_MATERIAL_DAILY_BILLING_FACT
     BILLING_DOCUMENT_LINE_TYPE_ID
     CURRENCY_CONVERSION_ID
     MATERIAL_ID
     SO_DOCUMENT_LINE_TYPE_ID
     BILLING_DATE_ID
    ADJ_ITEM_COST_LC_AMOUNT

How are you trying to display this calculation, what visual and other metrics are adding to the visual?

Tomas

[wildrose]

Yes I do have the model as you indicated in place. 

[Anonymous]

Hi Wildrose

You have a relationship as shown below:

SHARED_AREA.BILLING_DOCUMENT_LINE_TYPE_REF a
INNER JOIN SALES_AREA.CUSTOMER_MATERIAL_DAILY_BILLING_FACT b ON (b.BILLING_DOCUMENT_LINE_TYPE_ID=a.BILLING_DOCUMENT_LINE_TYPE_KEY
AND a.BILLING_LINE_ITEM_CATEGORY_CD NOT LIKE '%AE')

Power BI can only a single column relationship(active)

Is your wildcard only for this calculation?

SEARCH( "*AE", BILLING_DOCUMENT_LINE_TYPE_REF[BILLING_LINE_ITEM_CATEGORY_CD NOT LIKE], 1, 0 )=0

calculate( sum(ADJ_ITEM_COST_LC_AMOUNT),
BILLING_DOCUMENT_LINE_TYPE_REF[BILLING_LINE_ITEM_CATEGORY_CD] IN {"TANN","YBAN","YB1N","RENN","ZGNN","ZLNN"},
MATERIAL_DIM[MATERIAL_HIERARCHY_LEVEL3_CD] NOT IN {"CH000CH090CH090025"},
SO_DOCUMENT_LINE_TYPE_REF[SO_DOCUMENT_REASON_CD] NOT IN {"Z17","Z18","Z19","Z96","Z97","Z98"})
*Calculate(Sum(CURRENCY_CONVERSION_REF[EXCHANGE_RATE]))

use Slicer to select the date that you want to display

Tomas

[wildrose]

Spoiler

Hi Tomas,

The "FROM" clause is what is being used when bringing the data into Power BI so it is not necessary to be part of the calculation as well as the date.

I am focusing on this part which will be a measure in the model. Looking to convert this SQL calculation to a DAX calculation.

sum((
CASE WHEN (
CASE WHEN a.BILLING_LINE_ITEM_CATEGORY_CD IN ('TANN','YBAN','YB1N','RENN','ZGNN','ZLNN')
and d.MATERIAL_HIERARCHY_LEVEL3_CD NOT IN ('CH000CH090CH090025')
and e.SO_DOCUMENT_REASON_CD NOT IN ('Z17','Z18','Z19','Z96','Z97','Z98')
THEN 1 ELSE 0 END

)=1 THEN (b.ADJ_ITEM_COST_LC_AMOUNT) ELSE 0.00 END ) * c.EXCHANGE_RATE)

Hope that clarifies what I am doing. (or trying to do) 🙂

[Anonymous]

Hi

Have you try the following:

calculate( sum(ADJ_ITEM_COST_LC_AMOUNT),
BILLING_DOCUMENT_LINE_TYPE_REF[BILLING_LINE_ITEM_CATEGORY_CD] IN {"TANN","YBAN","YB1N","RENN","ZGNN","ZLNN"},
MATERIAL_DIM[MATERIAL_HIERARCHY_LEVEL3_CD] NOT IN {"CH000CH090CH090025"},
SO_DOCUMENT_LINE_TYPE_REF[SO_DOCUMENT_REASON_CD] NOT IN {"Z17","Z18","Z19","Z96","Z97","Z98"})
*Calculate(Sum(CURRENCY_CONVERSION_REF[EXCHANGE_RATE]))

The first calculation will return either a value or blank if not true

The second will return the exchange rate

Last we multiply the two results.

Did it help

<p><strong>Tomas Santandreu Polanco |Principal Business Intelligence Consultant</strong><br><a href="http://www.designmind.com/"><strong>www.designmind.com</strong></a><br></p>

[wildrose]

I needed to tweak the code to run so I changed it to this: (Changed the NOT location)

Free_Goods = calculate( sum(CUSTOMER_MATERIAL_DAILY_BILLING[ADJ_ITEM_COST_LC_AMOUNT]),
BILLING_DOCUMENT_LINE_TYPE_REF[BILLING_LINE_ITEM_CATEGORY_CD] IN {"TANN","YBAN","YB1N","RENN","ZGNN","ZLNN"},
NOT MATERIAL[MATERIAL_HIERARCHY_LEVEL3_CD] IN {"CH000CH090CH090025"},
NOT SO_DOCUMENT_LINE_TYPE_REF[SO_DOCUMENT_REASON_CD] IN {"Z17","Z18","Z19","Z96","Z97","Z98"})
*Calculate(Sum(CURRENCY_CONVERSION_REF[EXCHANGE_RATE]))

While this ran it did not return the correct results. I "think" the problem is with the SUM. The number I want to return is $14,324.58. This calc is returning $1,733,645.99. The multiplication needs to be at the line level and then summed up. I tried playing with the code but wasn't able to get it right. I think we are close.

[Anonymous]

Hi Wildrose

Can you share what is the configuration of the visual you are using?

Slicer

Visual

       Type: Matric, Stacked column, table, clustered column

      what fields are in the visual

Thank you

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

[wildrose]

It is a table. I am trying to add to the above. 

[Anonymous]

Hi Wildrose,

Do you get correct values when you break the calculated measure into two calculated measure and add the line to table?

Tomas

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

[wildrose]

Hi Tomas,

Thank you for all your hints and suggestions. This is what worked. I am grateful for your help. 

CALCULATE( SUMX(CUSTOMER_MATERIAL_DAILY_BILLING,'CUSTOMER_MATERIAL_DAILY_BILLING'[ADJ_ITEM_COST_LC_AMOUNT]*CUSTOMER_MATERIAL_DAILY_BILLING[EXCHANGE_RATE]),

BILLING_DOCUMENT_LINE_TYPE_REF[BILLING_LINE_ITEM_CATEGORY_CD] IN {"TANN","YBAN","YB1N","RENN","ZGNN","ZLNN"},

NOT MATERIAL[MATERIAL_HIERARCHY_LEVEL3_CD] IN {"CH000CH090CH090025"} , NOT SO_DOCUMENT_LINE_TYPE_REF[SO_DOCUMENT_REASON_CD] IN {"Z17","Z18","Z19","Z96","Z97","Z98"}

)

[Anonymous · Accepted solution]

Great to have help in some ways.

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com