Forum Discussion

wildrose's avatar
wildrose
Frequent Visitor
6 years ago
Solved

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

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'

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      wildrose
      Frequent Visitor
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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