Adding a column or table with skipped rows in Desktop

[Anonymous]

Hi again!

 I was able to do adapt a solution I got from an user here using Power Query, but it turns out that the actual file is really heavy, so it takes way too much time to load, so now I'm trying to replicate it in- Desktop with Dax.

 I'm trying to get which rows are getting skipped and I would like to add them to a new column or a new table. For example, in the new column on the 2nd row should say "2232-2233-2234-2235-2236".

I tried using a while loop but I'm missing how to actually write different values in the same row of the new column.

I'd appreciate any advice.

Thanks in advance for any help!

[v-henryk-mstf]

Hi @Anonymous ,

One small suggestion for formula optimization is to use the performance analyzer to check the performance of the report elements (e.g. visual objects and execution of DAX formulas).

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Microsoft Docs

Best Regards,
Henry

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

[Anonymous]

I tried to apply it but it didn't seem to solve my problem. Thank you anyway. I didn't know about that tool and I think it will be useful in the future.

[johnt75 · Accepted solution]

You can try the below, but it may not perform well on your large dataset

Missing Values = 
var currentValue = 'Table'[Nro.Comprob.]
var prevValue = SELECTCOLUMNS( TOPN(1, FILTER( ALL('Table'), 'Table'[Nro.Comprob.] < currentValue), 'Table'[Index], DESC), "@val", 'Table'[Nro.Comprob.])
return IF( 'Table'[Index] > 1, CONCATENATEX( GENERATESERIES( prevValue + 1, currentValue - 1), [Value], "-"))

The CONCATENATEX .. GENERATESERIES principle should work fine, but you may need a more performant way of getting the previous value.

[Anonymous]

@johnt75 

Sorry, just one more question about your solution.
I replaced the way of getting the previous value by just adding a new column with that previous value, so it would be something like:

var currentValue = 'Table'[Nro.Comprob.]
var prevValue = 'Table'[PreviousValue]

So it doesn't have to do all the calculation. However, I still getting the memory usage issue. Is there a possibility that in this case the data set is too large, no matter what function is being used?

Thanks again.

[johnt75]

It sounds like it is. The only other thing I can think of is to try limiting the data you load into Power BI Desktop by using Power Query parameters and then try publishing to the service and load in all the data, see if the service can handle it. Don't know whether that would work in your circumstances

[Anonymous]

Oh, I see. Thank you very much!

[Anonymous]

 Yes, indeed it didn't perform well when I tried it. The result doesn't even show up because memory isn't enough haha But I think it's a problem with my data being so large, not the code. I'll try to think a different aproach.

Thank you!