cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## CountIf in dax with fixed row

Dear Community,

how can I transfer this formula in excel to power bi Dax:

(30-COUNTIF(AF\$61:AF61,"<>-"))

The first value is fixed in the column AF, and the range changes by 1 as it goes, so the next row is calculated like this:  COUNTIF(AF\$61:AF62,"<>-"))  and so on as it goes to the next rows.

Thank you very much.

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

``````Measure =
VAR CurDate_ =
MAX ( 'YourTable'[Date] )
RETURN
30
- CALCULATE (
COUNT ( 'YourTable'[ColumnName] ),
'YourTable'[Date] <= CurDate_,
'YourTable'[ColumnName] <> "-"
)
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support

Hi @Anonymous ,

``````Measure =
VAR CurDate_ =
MAX ( 'YourTable'[Date] )
RETURN
30
- CALCULATE (
COUNT ( 'YourTable'[ColumnName] ),
'YourTable'[Date] <= CurDate_,
'YourTable'[ColumnName] <> "-"
)
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Memorable Member

Hi,

So you're looking for a cumulative count with conditions ?

Do you have an index or a date in your data to do cumulative ?

Can you show us some dummy datas so we can help ?

Basically if you want a count per year you can use Time INtellignece function like TOTALYTD, or a CALCULATE with filters in it.

For example :

`TOTALYTD([Sales Revenue],Sales[Date Invoice],//Cumulative sum until today from 1st januaryALL(TableDates))//For all dates`
or for cumulative with index :
`SUM running by lane (Measure) = VAR CurrentIndex=SELECTEDVALUE( 'TabData'[Index] )//Current row indexVAR CurrentLane = SELECTEDVALUE( 'TabData'[LaneID] )//Current LaneRETURNCALCULATE( SUMX( 'TabData' , 'TabData'[value] ),//Sum valuesALL( 'TabData'[Date operation] ),//for all dates'TabData'[Index] <= CurrentIndex,//only for rows before'TabData'[LaneID] = CurrentLane )//and for the lane computed`

Hope those patterns will help
Anonymous
Not applicable

Hi AilleryO,

Thank you so much for your reply! I do have date column in my data.
Can youplease explain this part

`'TabData'[Index] <= CurrentIndex,//only for rows before'TabData'[LaneID] = CurrentLane )//and for the lane computed`

a bit more in detail?

Thank you!

Memorable Member

In my case I have an index column on my data with unique number increasing.

So to get a cumulative result, I'm doing the sum of all previous lines (index wise).

In other words, filtering all line before the one being computed (CurrentRow).

The other one with the lane is that in my case I was making a cumulative sum related to diffrent lanes, so I have to filter as well on the same lane as the one being computed (CurrentLane).

So if you have a date and no other filter, you just need the Index part of my example but adapted to your date.

Create a variable

`CurrentDate = MAX( YourDate) or SELECTEDVALUE( YourDate )`

And then use it  :

`'TabData'[Date] <= CurrentDate`

Hope it makes things clearer