Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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
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
)
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
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.
You put me on the right track!
Thanks!
Here is what I did...
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
103 | |
77 | |
71 |