Forum Discussion

tanako123's avatar
tanako123
New Member
8 years ago
Solved

From Tableau to PowerBI DAX

Hello 

 

I am currenctly trying to get diffrent reports from Tableau to DAX. I am really a beginner at DAX but can someone exaplain what the formula below in Tabluea does and help me convert it to PowerBI DAX. I think fixed does something with the level of detail??

 

 

IF { FIXED [FormulierId]:MAX(CONTAINS([Type melding (group)],'Reguliere melding'))} THEN { FIXED [FormulierId]:MAX('Reguliere melding')}
ELSEIF { FIXED [FormulierId]:MAX(CONTAINS([Type melding (group)],'Politiemelding'))} THEN { FIXED [FormulierId]:MAX('Politiemelding')}
END

 

many thanks!!

  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi tanako123

     

    Your Tableau formula is the equivalent of a calculated column that says:

    1. For each row, examine all rows with [FormulierId] equal to the value in the current row
    2. If any of those rows has [Type melding (group)] with substring 'Reguliere melding' then return 'Reguliere melding'
    3. Else if any of those rows has [Type melding (group)] with substring 'Politiemelding' then return 'Politiemelding'
    4. Else return Null

     

    The DAX equivalent would have to be a calculated column with code like this (I'm calling the table Data):

     

    Calculated Column =
IF (
    NOT (
        ISEMPTY (
            CALCULATETABLE (
                VALUES ( Data[Type melding (group)] ),
                ALLEXCEPT ( Data, Data[FormulierId] ),
                NOT ( ISERROR ( FIND ( "Reguliere melding", Data[Type melding (group)] ) ) )
            )
        )
    ),
    "Reguliere melding",
    IF (
        NOT (
            ISEMPTY (
                CALCULATETABLE (
                    VALUES ( Data[Type melding (group)] ),
                    ALLEXCEPT ( Data, Data[FormulierId] ),
                    NOT ( ISERROR ( FIND ( "Politiemelding", Data[Type melding (group)] ) ) )
                )
            )
        ),
        "Politiemelding"
    )
)

     

    By the way, the Tableau formula is written a little strangely, since

     

    { FIXED [FormulierId]:MAX('Reguliere melding')}

    could be replaced with

    'Reguliere melding'

     but this could be because it started off as something more complex.

     

    Regards,

    Owen

3 Replies

  • tanako123's avatar
    tanako123
    New Member
    8 years ago

    I have to move reports from Tableau to PowerBI, i came along this formula but what exactly did he do? 

    can someone please help me convert this to PowerBI DAX

     

     

    IF { FIXED [FormulierId]:MAX(CONTAINS([Type melding (group)],'Reguliere melding'))} THEN { FIXED [FormulierId]:MAX('Reguliere melding')}
    ELSEIF { FIXED [FormulierId]:MAX(CONTAINS([Type melding (group)],'Politiemelding'))} THEN { FIXED [FormulierId]:MAX('Politiemelding')}
    END

     

    Thanks!

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi tanako123,

     

    Honestly, I haven't used Tableau before. So I'm not familiar with the formula in Tabluea or even convert it to DAX.

     

    Thereby, could you please make a explaination about the logic of this formula and share us some sample data and the expected result? We can create a new DAX formula based on these information for you. :smileyhappy:

     

    Thanks,
    Xi Jin.

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    Hi tanako123

     

    Your Tableau formula is the equivalent of a calculated column that says:

    1. For each row, examine all rows with [FormulierId] equal to the value in the current row
    2. If any of those rows has [Type melding (group)] with substring 'Reguliere melding' then return 'Reguliere melding'
    3. Else if any of those rows has [Type melding (group)] with substring 'Politiemelding' then return 'Politiemelding'
    4. Else return Null

     

    The DAX equivalent would have to be a calculated column with code like this (I'm calling the table Data):

     

    Calculated Column =
IF (
    NOT (
        ISEMPTY (
            CALCULATETABLE (
                VALUES ( Data[Type melding (group)] ),
                ALLEXCEPT ( Data, Data[FormulierId] ),
                NOT ( ISERROR ( FIND ( "Reguliere melding", Data[Type melding (group)] ) ) )
            )
        )
    ),
    "Reguliere melding",
    IF (
        NOT (
            ISEMPTY (
                CALCULATETABLE (
                    VALUES ( Data[Type melding (group)] ),
                    ALLEXCEPT ( Data, Data[FormulierId] ),
                    NOT ( ISERROR ( FIND ( "Politiemelding", Data[Type melding (group)] ) ) )
                )
            )
        ),
        "Politiemelding"
    )
)

     

    By the way, the Tableau formula is written a little strangely, since

     

    { FIXED [FormulierId]:MAX('Reguliere melding')}

    could be replaced with

    'Reguliere melding'

     but this could be because it started off as something more complex.

     

    Regards,

    Owen