cancel
Showing results for
Did you mean:
Regular Visitor

## DAX query in direct mode

Hi All,

The below query doesnt work in direct query mode  for Calculate and All except:

Column = Table[ColD] - Calculate(MIN(Table[ColD]), ALLEXCEPT(Table, Table[ColA],Table[ColB],Table[ColC))+1

Can anyone help with alternatives ?

Thank you
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: DAX query in direct mode

Calculate is not work in a calculated column in direct query. please change it to measure using below formula:

```Measure = var a = CALCULATE(MIN(Table[ColD]),ALLEXCEPT(Table,Table[ColA],Table[ColB],Table[ColC]))
Return
MAX(Table[ColD])-a+1```

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

## Re: DAX query in direct mode

I tried all methods as far as my knowledge, but still yet to access this because of the limitations on DAX and M code under DQ mode. I think you may change it to import mode for more performance.

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

## Re: DAX query in direct mode

Calculate is not work in a calculated column in direct query. please change it to measure using below formula:

```Measure = var a = CALCULATE(MIN(Table[ColD]),ALLEXCEPT(Table,Table[ColA],Table[ColB],Table[ColC]))
Return
MAX(Table[ColD])-a+1```

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Regular Visitor

## Re: DAX query in direct mode

Hi @v-diye-msft ,

Thank you for reply. This caculation needs to be as axis in one of the visuals , but since its a measure I am not able to do so .

Is there any possible workaround for this ?

Regards

@v-diye-msft wrote:

Calculate is not work in a calculated column in direct query. please change it to measure using below formula:

```Measure = var a = CALCULATE(MIN(Table[ColD]),ALLEXCEPT(Table,Table[ColA],Table[ColB],Table[ColC]))
Return
MAX(Table[ColD])-a+1```

Community Support Team

## Re: DAX query in direct mode

I tried all methods as far as my knowledge, but still yet to access this because of the limitations on DAX and M code under DQ mode. I think you may change it to import mode for more performance.

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

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!