Forum Discussion
GENERATESERIES by boundaries
Hi forum,
I have a question to you regarding following scenario:
Table Dim_SKR4_Account_Group contains financial account groups, defined by ranges.
Dim_SKR4_Chart_of_Accounts contains all accounts (unique):
To relate both, I want to create following mapping table Mapp_Populated_Account_Range:
Mapp_Populated_Account_Range =
As you can see, DAX doesnt apply the boundaries.
Therefore I get duplicates.
I worked with CALENDAR() before and didnt face this problem.
I thought GENERATESERIES would work in a similar way.
Is there a way to fix the DAX code or do I have to choose a workaround?
Before this mapping logic was implemented via conditional merge in PQ, but I want to do it in DAX, if possible.
Many thanks,
DataHero
Hi forum,
I found a solution now for my specific case:Mapp_Populated_Account_Series =
GENERATE (
SELECTCOLUMNS (
'Dim_SKR4_Account_Group',
"Account lower boundary", Dim_SKR4_Account_Group[KontoNrUntergrenze],
"Account upper boundary", Dim_SKR4_Account_Group[KontoNrObergrenze]
),
GENERATESERIES (
[Account lower boundary],
[Account upper boundary],
1
)
)
When you use GENERATESERIES, attention is to be paid in case you want to rename columns withing this code. DAX can easily loose the context here.
To avoid errors, the code is to be written as for the column “test” in case of renaming.Mapp_Populated_Account_Series =
GENERATE (
SELECTCOLUMNS (
'Dim_SKR4_Account_Group',
"test", Dim_SKR4_Account_Group[KontoNrUntergrenze],
[KontoNrObergrenze]
),
GENERATESERIES (
[test],
'Dim_SKR4_Account_Group'[KontoNrObergrenze],
1
)
)
)
To avoid problems, I recommend to rename already in PQ.
3 Replies
Hi DataHero
if you only want to link the matching group to each Konto you may try to add a new custom column in Dim_SKR4_Chart_of_Accounts like this:Gruppe = CALCULATE( MIN( Dim_SKR4_Account_Group[Kontengruppe] ), FILTER( Dim_SKR4_Account_Group, Dim_SKR4_Account_Group[KontoNrUntergrenze] <= Dim_SKR4_Chart_of_Accounts[KontoNr] ), FILTER( Dim_SKR4_Account_Group, Dim_SKR4_Account_Group[KontoNrObergrenze] >= Dim_SKR4_Chart_of_Accounts[KontoNr] ) )Hope this helps!
Grüße
Tim
The problem is that the MIN and MAX are being calculated without any filter or row context. Try
All accounts = GENERATE ( SELECTCOLUMNS ( Dim_SKR4_Account_Group, Dim_SKR4_Account_Group[KontoNrUntergrenze], Dim_SKR4_Account_Group[KontoNrObergrenze] ), GENERATESERIES ( Dim_SKR4_Account_Group[KontoNrUntergrenze], Dim_SKR4_Account_Group[KontoNrObergrenze] ) )Hi forum,
I found a solution now for my specific case:Mapp_Populated_Account_Series =
GENERATE (
SELECTCOLUMNS (
'Dim_SKR4_Account_Group',
"Account lower boundary", Dim_SKR4_Account_Group[KontoNrUntergrenze],
"Account upper boundary", Dim_SKR4_Account_Group[KontoNrObergrenze]
),
GENERATESERIES (
[Account lower boundary],
[Account upper boundary],
1
)
)
When you use GENERATESERIES, attention is to be paid in case you want to rename columns withing this code. DAX can easily loose the context here.
To avoid errors, the code is to be written as for the column “test” in case of renaming.Mapp_Populated_Account_Series =
GENERATE (
SELECTCOLUMNS (
'Dim_SKR4_Account_Group',
"test", Dim_SKR4_Account_Group[KontoNrUntergrenze],
[KontoNrObergrenze]
),
GENERATESERIES (
[test],
'Dim_SKR4_Account_Group'[KontoNrObergrenze],
1
)
)
)
To avoid problems, I recommend to rename already in PQ.
