GENERATESERIES by boundaries

[DataHero]

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:

Here the code:
Mapp_Populated_Account_Range =

VAR MinAccount = MINX('Dim_SKR4_Account_Group','Dim_SKR4_Account_Group'[KontoNrUntergrenze])

VAR MaxAccount = MAXX('Dim_SKR4_Account_Group','Dim_SKR4_Account_Group'[KontoNrObergrenze])

RETURN

GENERATE (

    SELECTCOLUMNS(

        'Dim_SKR4_Account_Group',

        "Account lower boundary", [KontoNrUntergrenze],

        "Account upper boundary", [KontoNrObergrenze]

    ),

    GENERATESERIES(MinAccount, MaxAccount, 1)

)

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

[DataHero · Accepted solution]

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.

[johnt75]

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]
    )
)

[timalbers]

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