cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SonaSingh123
Post Patron
Post Patron

DAX HELP

Below formula giving wrong value.
Please help
 
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


@shebr wrote:

Hi @SonaSingh123 

 

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?

View solution in original post

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 screenshotsScreenshot (6).pngScreenshot (8).png

Relationship is wrong.

Join Date column with Grace Period End Date

az38
Super User
Super User

Hi @SonaSingh123 

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
LinkedIn

SO IDCLASSIFICATIONFLAGQTYQTY2
1C&I0 100
2EBO1200 
3EBO1100 
4C&I1 500
5C&I0 800

Hi @SonaSingh123 

 

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))


@shebr wrote:

Hi @SonaSingh123 

 

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?

View solution in original post

SO IDCUSTOMERCLASSIFICATIONFLAGQTYQTY2
1C1C&I0 100
2C2EBO1200 
3C2EBO1100 
4C1C&I1 500
5C1C&I0 800
      
RESULT     
CUSTOMERFINAL QTY    
C11400    
C2300    

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

 

sonasingh.PNG

 

SO IDCUSTOMERCLASSIFICATIONFLAGQTYQTY2
1C1C&I0 100
2C2EBO1200 
3C2EBO1100 
4C2EBO0400 
5C1C&I1 500
6C1C&I0 800
      
EXPECTED RESULT    
CUSTOMERFINAL QTY    
C11400    
C2300    

 

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

This is the issue am facing.

Hi @SonaSingh123 

 

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

SO IDCUSTOMERCLASSIFICATIONFLAGQTYQTY2
4C2EBO0400 

 

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.

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

 

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 ))

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

 

Capture2.PNG

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

No Problem, I thought I was going crazy.


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

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

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

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. 

 

Same result bro.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.