Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX HELP

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DAX HELP

05-04-2020
06:51 AM

Below formula giving wrong value.

Please help

INVOICED QTY =

CALCULATE(SUM(SO_BI_FACT[QTY]),

FILTER(SO_BI_FACT,

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
08:58 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
07:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
08:04 AM

Relationship is wrong.

Join Date column with Grace Period End Date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
07:05 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
07:22 AM

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
07:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
08:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
07:58 AM

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
08:12 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
08:55 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
09:03 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
09:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
09:26 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
09:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
10:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-05-2020
04:14 AM

No Problem, I thought I was going crazy.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
09:49 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
09:58 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
07:16 AM

Same result bro.