topic Re: CountIf in dax with fixed row in DAX Commands and Tips

CountIf in dax with fixed row

Anonymous — Tue, 10 May 2022 09:32:40 GMT

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.

Re: CountIf in dax with fixed row

AilleryO — Tue, 10 May 2022 10:01:00 GMT

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 january
ALL(TableDates))//For all dates

or for cumulative with index :

SUM running by lane (Measure) = 
VAR CurrentIndex=SELECTEDVALUE( 'TabData'[Index] )//Current row index
VAR CurrentLane = SELECTEDVALUE( 'TabData'[LaneID] )//Current Lane
RETURN
CALCULATE( 
SUMX( 'TabData' , 'TabData'[value] ),//Sum values
ALL( '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

Re: CountIf in dax with fixed row

Anonymous — Tue, 10 May 2022 10:12:28 GMT

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!

Re: CountIf in dax with fixed row

AilleryO — Tue, 10 May 2022 10:47:19 GMT

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

Re: CountIf in dax with fixed row

Icey — Tue, 17 May 2022 07:21:20 GMT

Hi Anonymous ,

Please check this:

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.