Convert from a range of GL accounts to another GL account for a selection of dimensions

[ChrisHokstad]

Hi

I have all my GL data in a SQL database, but for presentation purposes in PBI, I need to convert some of the accounts from Payroll (5000-series) to COGS (4000 series), and only for 2 out of 50 departmens (dimensions). See below how I think. I need to convert a range og accounts (5000-5999) for department 211 and 212 to account 4350:

Is this even possible?

Thanks,

Chris

[amitchandak]

@ChrisHokstad , A new column

if( [Account] = 5010 && [Department] in {211,212}  , 4350, [Account])

In case columns are text the use double quote , like = "5010"

[ChrisHokstad]

Thanks @amitchandak , this works, but how could I use this formula on a range of accounts i.e from account 5000..5999?

[Icey · Accepted solution]

Hi @ChrisHokstad ,

Try this:

Column =

VAR AccountRange_ = GENERATESERIES(5000,5999,1)

RETURN

if( [Account] IN AccountRange_ && [Department] in {211,212} , 4350, [Account])

 

Best Regards,

Icey

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

[ChrisHokstad]

Thanks alot @Icey , this worked fine!