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

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

       

       

       

       

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