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 records dont match 

this is the result im looking for.

and this is what i get (some of the criteria are misclassified)

 

 

Power Query Code

criteria 90%
last year - CP5
Last 2 yeasr - CP 4
last 5 years - CP 2

if [#"Fecha "] > #date(2022,1,1) and [Cumple]>90 then "CP5"
else if [#"Fecha "] > #date(2020,12,30) and [Cumple]>90 then "CP4"
else if [#"Fecha "] > #date(2017,1,1) and [Cumple]>90 then "CP2"

Criteria 60%
last year - CP4
Last 2 yeasr - CP 3
last 5 years - CP 1

else if [#"Fecha "] > #date(2022,1,1) and [Cumple]>60 then "CP4"
else if [#"Fecha "] > #date(2020,1,1) and [Cumple]>60 then "CP3"
else if [#"Fecha "] > #date(2017,1,1) and [Cumple]>60 then "CP1"

else "Null"

 

any help or advice is appreciated 

  • 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.

2 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi A7Russ ,

    Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

     

    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.