cancel
Showing results for
Did you mean:
Helper II

## If statement with count

Hi Dev,

I am trying to write a DAX query that shows me value of 0. if sum of debit side of a vehicle equals to the credit side of that period.

My formula is below but it comes with an error

```Equal = IF(Sum(Query1[F_NOM_DEBIT]) = SUM(Query1[F_NOM_CREDIT]),
Format(0,ALLEXCEPT(Query1,Query1[F_NOM_VEH_ID],Query1[F_NOM_PERIOD])),
-1)```

But it comes with error "The expression refers to multiple columns. Multiple columns cannot be coverted to a scalar value"

So where it matches the total value of a specific ID example 10157 it should show 0 and where not it should show -1

Many Thanks

Jibran

1 ACCEPTED SOLUTION
Community Support

Hi @jibran,

Please try to create a measure like below:

```Equal = IF(CALCULATE(SUM(Table1[F_NOM_ CREDIT]), ALLEXCEPT(Table1,'Table1'[F_NOM_VEH_ID],'Table1'[F_NOM_PERIOD]))=CALCULATE(SUM(Table1[F_NOM_DEBIT]), ALLEXCEPT(Table1,'Table1'[F_NOM_VEH_ID],Table1[F_NOM_PERIOD])),
0,-1)```

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

Hi @jibran,

Please try to create a measure like below:

```Equal = IF(CALCULATE(SUM(Table1[F_NOM_ CREDIT]), ALLEXCEPT(Table1,'Table1'[F_NOM_VEH_ID],'Table1'[F_NOM_PERIOD]))=CALCULATE(SUM(Table1[F_NOM_DEBIT]), ALLEXCEPT(Table1,'Table1'[F_NOM_VEH_ID],Table1[F_NOM_PERIOD])),
0,-1)```

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

Hi,

Thanks for the help I was away from the office it has worked, but can I use filter for example show me only -1 related data or show me 0 related data.

Many Thanks

Jibran

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors