Forum Discussion
Calculation With Multiple Case Statements Over Multiple Tables - Convert SQL to DAX
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
Yes I do have the model as you indicated in place.
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
- SpoilerHi 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) 🙂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>
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.