How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query)

[GreggPowell]

How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query).

I have a table where the data is pulled from SharePoint. One column in the table I created from a measure

New Column = Table[Measure]

So, that column does not show up when I transform the data - so I can't use Power Query.

I want to use Dax to extract every Nth row (in my case exactly, the 1st and then every 7th row) into a new table.

Is that even possible?

[GreggPowell · Accepted solution]

Had already added an index column. Problem is, I can't just tell it the row numbers I want to pick, because an API updates the data every day with a new row - so the whole thing must be automated.

I found this below - this is what I need to do.....

Problem is with this Excel formula:

=FILTER(data,MOD(SEQUENCE(ROWS(data)),3)=0)

I can't find the equivelent Excel ROWS() function in DAX - so I can replicate this.

I think the ROWS() function is the only incompatibility between DAX and Excel - not sure though.

Can you (or anyone) help me rewrite this in DAX?

THANKS!!!
Gregg P.
Arizona USA

[bcdobbs]

Are you able to use power query to add an index column followed by a calculated column based on it using Number.Mod to your existing table?

You can then use Dax to create a calculated table along the lines of:

NewTable =
CALCULATETABLE (

 OriginalTable,

 ModColumn = 1

)

[GreggPowell · Accepted solution]

Had already added an index column. Problem is, I can't just tell it the row numbers I want to pick, because an API updates the data every day with a new row - so the whole thing must be automated.

I found this below - this is what I need to do.....

Problem is with this Excel formula:

=FILTER(data,MOD(SEQUENCE(ROWS(data)),3)=0)

I can't find the equivelent Excel ROWS() function in DAX - so I can replicate this.

I think the ROWS() function is the only incompatibility between DAX and Excel - not sure though.

Can you (or anyone) help me rewrite this in DAX?

THANKS!!!
Gregg P.
Arizona USA

[bcdobbs]

What I was suggesting basically is that. Only difference is I'd suggest creating the modulo column in power query and then using the calculatetable to filter based on it.

[bcdobbs · Accepted solution]

I'm away from

a computer until Tuesday evening. Happy to build a demo at that point if no one gets there first.

[GreggPowell]

You put me on the right track!

Thanks!

Here is what I did...

Extract_HR_PipeDrive_Deals = FILTER(ALL(HR_PipeDriveDeals_Running_Totals),HR_PipeDriveDeals_Running_Totals[Modulo]=6)