cancel
Showing results for
Did you mean:  Post Patron

## DAX HELP

Below formula giving wrong value.

INVOICED QTY =
CALCULATE(SUM(SO_BI_FACT[QTY]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] <> "C&I"
&& SO_BI_FACT[FLAG]=1 ))
+
CALCULATE(SUM(SO_BI_FACT[QTY2]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] = "C&I"))
1 ACCEPTED SOLUTION  Resolver III

@shebr wrote:

Can you try this as a measure? Let me know how you get on? Are you expecting 1700 as your value?

My Calc =
var one = CALCULATE(SUM(SO_BI_FACT[QTY]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] <> "C&I"
&& SO_BI_FACT[FLAG]=1 ))

VAR two = CALCULATE(SUM(SO_BI_FACT[QTY2]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] = "C&I"))

return CALCULATE(one + two, ALL(SO_BI_FACT))

@SonaSingh123 did you try this measure?

19 REPLIES 19 Anonymous
Not applicable

Hi,

Can someone help me with dax.

I created 2 measures that take the start date & end Date of the slicer.

StartDate = calculate(min(datetable[date]),allselected(datetable[date]))
EndDate = calculate(max(datetable[date]),allselected(datetable[date]))

I want to create a column that shows-that a particular card is expired or not.

Expired = IF([End Date] > 'Card Type'[GracePeriodEndDate] ,1,0)

I am using this formulae but this is giving 0 for all the rows.

Pls have look at this screenshots    Post Patron

Relationship is wrong.

Join Date column with Grace Period End Date  Super User

congrats 🙂

any details?

the most common error in your case - is not using ALL() inside filter. But it is the step in the dark from my side

``````INVOICED QTY =
CALCULATE(SUM(SO_BI_FACT[QTY]),
FILTER(ALL(SO_BI_FACT),
SO_BI_FACT[CLASSIFICATION] <> "C&I"
&& SO_BI_FACT[FLAG]=1 ))
+
CALCULATE(SUM(SO_BI_FACT[QTY2]),
ALL(SO_BI_FACT),
SO_BI_FACT[CLASSIFICATION] = "C&I")``````

do not hesitate to give a kudo to useful posts and mark solutions as solution  Post Patron
 SO ID CLASSIFICATION FLAG QTY QTY2 1 C&I 0 100 2 EBO 1 200 3 EBO 1 100 4 C&I 1 500 5 C&I 0 800  Resolver III

Can you try this as a measure? Let me know how you get on? Are you expecting 1700 as your value?

My Calc =
var one = CALCULATE(SUM(SO_BI_FACT[QTY]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] <> "C&I"
&& SO_BI_FACT[FLAG]=1 ))

VAR two = CALCULATE(SUM(SO_BI_FACT[QTY2]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] = "C&I"))

return CALCULATE(one + two, ALL(SO_BI_FACT))  Resolver III

@shebr wrote:

Can you try this as a measure? Let me know how you get on? Are you expecting 1700 as your value?

My Calc =
var one = CALCULATE(SUM(SO_BI_FACT[QTY]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] <> "C&I"
&& SO_BI_FACT[FLAG]=1 ))

VAR two = CALCULATE(SUM(SO_BI_FACT[QTY2]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] = "C&I"))

return CALCULATE(one + two, ALL(SO_BI_FACT))

@SonaSingh123 did you try this measure?  Post Patron
 SO ID CUSTOMER CLASSIFICATION FLAG QTY QTY2 1 C1 C&I 0 100 2 C2 EBO 1 200 3 C2 EBO 1 100 4 C1 C&I 1 500 5 C1 C&I 0 800 RESULT CUSTOMER FINAL QTY C1 1400 C2 300  Resolver III

Yep, this is the expected result with my last message:   Post Patron

 SO ID CUSTOMER CLASSIFICATION FLAG QTY QTY2 1 C1 C&I 0 100 2 C2 EBO 1 200 3 C2 EBO 1 100 4 C2 EBO 0 400 5 C1 C&I 1 500 6 C1 C&I 0 800 EXPECTED RESULT CUSTOMER FINAL QTY C1 1400 C2 300

This formula is adding fourth SO ID also, where flag is not equal to 1.

This is the issue am facing.  Resolver III

I have the exact expected result as you have stated. Same values.

I dont understand what further you need? Can you clarify what the measure is intended to do? At the moment, from what you have posted, you want to add the following

1) The SUM of QTY where CLASSIFICATION <> "C&I" AND FLAG = 1. With your data this is total of 300, correct?

you want to add this value to the following:

2) The SUM of QTY 2 where CLASSIFICATION = "C&I". With your data this is 1400.

Together this is 1700. As per your data.

What is the issue here?

Thanks

Shebr  Post Patron
 SO ID CUSTOMER CLASSIFICATION FLAG QTY QTY2 4 C2 EBO 0 400

Suppose a row with classification = EBO and FLAG = 0, then the measure is considering this qty also. But as per the formula no need to consider, because flag not equal to 1.  Resolver III

Of course if different values appear in the data you need to handle this in the measure. Either you can add it as an additional variable (VAR) See my example, or you can add it in the FILTER formula.

Does that make sense?

If you can provide the FULL specs then I can give a complete answer, otherwise it will not be complete.

Thanks  Post Patron
Already restricted in the below formula. But still it is considering flag = 0 also.
var one = CALCULATE(SUM(SO_BI_FACT[QTY]),
FILTER(SO_BI_FACT,
SO_BI_FACT[CLASSIFICATION] <> "C&I"
&& SO_BI_FACT[FLAG]=1 ))  Resolver III

If you see this image it is not including SO ID 4.   Post Patron

Sorry bro, formula is fine. Source data is wrong.  Resolver III

No Problem, I thought I was going crazy.

** Did this answer your question, mark as resolved and hit the Kudos button!**  Post Patron

Yes, the formula is ok. But still adding that qty also.

I dont know, why the formula is considering this record also.  Resolver III

Can you show me a screen shot of your data table? Do you have any filters applied? Are your data formats in the right format? Check if your 'Flag' is text or numeric.  Post Patron

Same result bro.   