Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous 

 

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

@Anonymous 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

Anonymous
Not applicable

Relationship is wrong.

Join Date column with Grace Period End Date

az38
Community Champion
Community Champion

Hi @Anonymous 

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
Anonymous
Not applicable

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

Hi @Anonymous 

 

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 @Anonymous 

 

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

@Anonymous did you try this measure?

Anonymous
Not applicable

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

Anonymous
Not applicable

 

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 @Anonymous 

 

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

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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!**

Anonymous
Not applicable

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. 

 

Anonymous
Not applicable

Same result bro.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.