Forum Discussion

A7Russ's avatar
A7Russ
Regular Visitor
4 years ago
Solved

Power Query to DAX

Hi every one, I'm struck and really struggling to build a logic in power BI,  I'm looking for a solution to reslove in DAX. i have this in my power query editor...and i dont work very well...some r...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi A7Russ ,

    Please refer to my pbix file to see if it helps you.

    Create a measure.

    Measure =
IF (
    MAX ( 'Table'[Cumple] ) > 90
        && MAX ( 'Table'[Fecha] ) > DATE ( 2022, 1, 1 ),
    "CP5",
    IF (
        MAX ( 'Table'[Cumple] ) > 90
            && MAX ( 'Table'[Fecha] ) > DATE ( 2020, 12, 30 ),
        "CP4",
        IF (
            MAX ( 'Table'[Cumple] ) > 90
                && MAX ( 'Table'[Fecha] ) > DATE ( 2017, 1, 1 ),
            "CP2",
            IF (
                MAX ( 'Table'[Cumple] ) > 60
                    && MAX ( 'Table'[Cumple] ) < 90
                    && MAX ( 'Table'[Fecha] ) > DATE ( 2022, 1, 1 ),
                "CP4",
                IF (
                    MAX ( 'Table'[Cumple] ) > 60
                        && MAX ( 'Table'[Cumple] ) < 90
                        && MAX ( 'Table'[Fecha] ) > DATE ( 2020, 1, 1 ),
                    "CP3",
                    IF (
                        MAX ( 'Table'[Cumple] ) > 60
                            && MAX ( 'Table'[Cumple] ) < 90
                            && MAX ( 'Table'[Fecha] ) > DATE ( 2017, 1, 1 ),
                        "CP1",
                        "Null"
                    )
                )
            )
        )
    )
)

    Or create a column.

    Column =
IF (
    'Table'[Cumple] > 90
        && 'Table'[Fecha] > DATE ( 2022, 1, 1 ),
    "CP5",
    IF (
        'Table'[Cumple] > 90
            && 'Table'[Fecha] > DATE ( 2020, 12, 30 ),
        "CP4",
        IF (
            'Table'[Cumple] > 90
                && 'Table'[Fecha] > DATE ( 2017, 1, 1 ),
            "CP2",
            IF (
                'Table'[Cumple] > 60
                    && 'Table'[Cumple] < 90
                    && 'Table'[Fecha] > DATE ( 2022, 1, 1 ),
                "CP4",
                IF (
                    'Table'[Cumple] > 60
                        && 'Table'[Cumple] < 90
                        && 'Table'[Fecha] > DATE ( 2020, 1, 1 ),
                    "CP3",
                    IF (
                        'Table'[Cumple] > 60
                            && 'Table'[Cumple] < 90
                            && 'Table'[Fecha] > DATE ( 2017, 1, 1 ),
                        "CP1",
                        "Null"
                    )
                )
            )
        )
    )
)

    If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi A7Russ ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
IF (
    MAX ( 'Table'[Cumple] ) > 90
        && MAX ( 'Table'[Fecha] ) > DATE ( 2022, 1, 1 ),
    "CP5",
    IF (
        MAX ( 'Table'[Cumple] ) > 90
            && MAX ( 'Table'[Fecha] ) > DATE ( 2020, 12, 30 ),
        "CP4",
        IF (
            MAX ( 'Table'[Cumple] ) > 90
                && MAX ( 'Table'[Fecha] ) > DATE ( 2017, 1, 1 ),
            "CP2",
            IF (
                MAX ( 'Table'[Cumple] ) > 60
                    && MAX ( 'Table'[Cumple] ) < 90
                    && MAX ( 'Table'[Fecha] ) > DATE ( 2022, 1, 1 ),
                "CP4",
                IF (
                    MAX ( 'Table'[Cumple] ) > 60
                        && MAX ( 'Table'[Cumple] ) < 90
                        && MAX ( 'Table'[Fecha] ) > DATE ( 2020, 1, 1 ),
                    "CP3",
                    IF (
                        MAX ( 'Table'[Cumple] ) > 60
                            && MAX ( 'Table'[Cumple] ) < 90
                            && MAX ( 'Table'[Fecha] ) > DATE ( 2017, 1, 1 ),
                        "CP1",
                        "Null"
                    )
                )
            )
        )
    )
)

Or create a column.

Column =
IF (
    'Table'[Cumple] > 90
        && 'Table'[Fecha] > DATE ( 2022, 1, 1 ),
    "CP5",
    IF (
        'Table'[Cumple] > 90
            && 'Table'[Fecha] > DATE ( 2020, 12, 30 ),
        "CP4",
        IF (
            'Table'[Cumple] > 90
                && 'Table'[Fecha] > DATE ( 2017, 1, 1 ),
            "CP2",
            IF (
                'Table'[Cumple] > 60
                    && 'Table'[Cumple] < 90
                    && 'Table'[Fecha] > DATE ( 2022, 1, 1 ),
                "CP4",
                IF (
                    'Table'[Cumple] > 60
                        && 'Table'[Cumple] < 90
                        && 'Table'[Fecha] > DATE ( 2020, 1, 1 ),
                    "CP3",
                    IF (
                        'Table'[Cumple] > 60
                            && 'Table'[Cumple] < 90
                            && 'Table'[Fecha] > DATE ( 2017, 1, 1 ),
                        "CP1",
                        "Null"
                    )
                )
            )
        )
    )
)

If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

 

Best Regards

Community Support Team _ Polly

 

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