Problemns with IF logic VAR

beldev · ‎07-23-2024

Hello everyone, who are you guys doing?

I am preparing a comparative dashboard about two dev platforms and encountered a logical problem. Basically, I have to apply a conditional logic within DAX which I thought would be easy. Every time I filter a value (visual filter) on the page, it will perform a calculation by taking fixed values and multiplying them by the value selected in the filter.

So, hypothetically speaking, if my Project A does not have available data, I have to perform this calculation for it: (Fixed Jenkins Median / Fixed Azure Median) * Filtered project median value.

The measure was implemented as follows: if the calculation needs total values from both platforms, and when I filter by project, I created a VAR for the total of Azure and Jenkins but FIXED, so I can derive the values by division. VAR for acronyms and VAR totals were also created to perform the other calculations.

Has anyone done something similar and can help me?

Here is the DAX with logic:

// CONDITIONAL
    VAR JenkinsResult =
        IF(
            ISFILTERED(dGeneral[Acronym 2]),
            IF(
                ISBLANK(FilteredJenkinsMedian),
                (FixedJenkinsTotalMedian / FixedAzureTotalMedian) * AzureAcronymMedian
            ),
            TotalJenkinsMedian
        )

    VAR Platform = SELECTEDVALUE(dGeneral[Platform])

    // SWITCH
    VAR FinalResult =
        SWITCH(
            Platform,
            "Azure", TotalAzureMedian,
            "Jenkins", JenkinsResult
        )

RETURN
    FinalResult

Anonymous · ‎07-23-2024

Hi @beldev

Your code logic seems to be fine.

Here are some suggestions you can try:

Ensure that the filter context is correctly applied.

Verify the correctness of the code at each step.

Instead of ISBLANK, you can use COALESCE to handle BLANK values more efficiently.

You can try to modify the code. For example,

// CONDITIONAL
VAR JenkinsResult =
    IF(
        ISFILTERED(dGeneral[Acronym 2]),
        COALESCE(
            FilteredJenkinsMedian,
            (FixedJenkinsTotalMedian / FixedAzureTotalMedian) * AzureAcronymMedian
        ),
        TotalJenkinsMedian
    )

VAR Platform = SELECTEDVALUE(dGeneral[Platform])

// SWITCH
VAR FinalResult =
    SWITCH(
        Platform,
        "Azure", TotalAzureMedian,
        "Jenkins", JenkinsResult
    )

RETURN
    FinalResult

If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

Regards,

Nono Chen

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

beldev · ‎07-24-2024

So, this is DAX, I tried to put COALESCE and also reformulate the measure, putting the variables in steps to get the results and it still didn't work.

I tested with a project that had all the values, both for Jenkins and Azure, and also for the specific Azure project, but even so it doesn't do the calculation for the same acronym, only for Jenkins. I really don't know where to move anymore.

MedianSecondsTest =

    // JENKINS MEDIAN FIXED VALUE
    VAR JenkinsMedianTotalFixed =
        CALCULATE(
            MEDIANX(
                FILTER(
                    dGeral,
                    dGeral[Platform] = "Jenkins" &&
                    dGeral[Altered Stage Name] = "Build" &&
                    dGeral[Altered Pipe Status] = "Success"
                ),
                dGeral[Total Pipeline Seconds]
            ),
            ALL(dGeral[Acronym 2])
        ) +
        CALCULATE(
            MEDIANX(
                FILTER(
                    dGeral,
                    dGeral[Platform] = "Jenkins" &&
                    dGeral[Altered Stage Name] = "Deploy" &&
                    dGeral[Altered Pipe Status] = "Success"
                ),
                dGeral[Total Pipeline Seconds]
            ),
            ALL(dGeral[Acronym 2])
        ) +
        CALCULATE(
            MEDIANX(
                FILTER(
                    dGeral,
                    dGeral[Platform] = "Jenkins" &&
                    dGeral[Altered Stage Name] = "Package" &&
                    dGeral[Altered Pipe Status] = "Success"
                ),
                dGeral[Total Pipeline Seconds]
            ),
            ALL(dGeral[Acronym 2])
        )
    // JENKINS MEDIAN FIXED VALUE
 
    // JENKINS TOTAL MEDIAN  
    VAR JenkinsMedianTotal =
        CALCULATE(
            MEDIANX(
                FILTER(
                    dGeral,
                    dGeral[Platform] = "Jenkins" &&
                    dGeral[Altered Stage Name] = "Build" &&
                    dGeral[Altered Pipe Status] = "Success"
                ),
                dGeral[Total Pipeline Seconds]
            )
        ) +
        CALCULATE(
            MEDIANX(
                FILTER(
                    dGeral,
                    dGeral[Platform] = "Jenkins" &&
                    dGeral[Altered Stage Name] = "Deploy" &&
                    dGeral[Altered Pipe Status] = "Success"
                ),
                dGeral[Total Pipeline Seconds]
            )
        ) +
        CALCULATE(
            MEDIANX(
                FILTER(
                    dGeral,
                    dGeral[Platform] = "Jenkins" &&
                    dGeral[Altered Stage Name] = "Package" &&
                    dGeral[Altered Pipe Status] = "Success"
                ),
                dGeral[Total Pipeline Seconds]
            )
        )
    // JENKINS TOTAL MEDIAN
 
    // AZURE FIXED VALUE MEDIAN
    VAR AzureMedianTotalFixed =
        CALCULATE(
            MEDIANX(
                FILTER(
                    dGeral,
                    dGeral[Platform] = "Azure" &&
                    dGeral[Total Pipeline Seconds] <= 604800
                ),
                dGeral[Total Pipeline Seconds]
            ),
            ALL(dGeral[Acronym 2])
        )
 
    // AZURE TOTAL MEDIAN
    VAR AzureMedianTotal =
        CALCULATE(
            MEDIANX(
                FILTER(
                    dGeral,
                    dGeral[Platform] = "Azure" &&
                    dGeral[Total Pipeline Seconds] <= 604800
                ),
                dGeral[Total Pipeline Seconds]
            )
        )
 
    // AZURE TOTAL MEDIAN BY ACRONYM
    VAR AzureMedianAcronym =
        IF(
            ISFILTERED(dGeral[Acronym 2]),
            CALCULATE(
                AzureMedianTotal,
                dGeral[Acronym 2] IN VALUES(dGeral[Acronym 2])
            ),
            BLANK()
        )
 
    // JENKINS TOTAL MEDIAN BY ACRONYM
    VAR FilteredJenkinsMedian =
        IF(
            ISFILTERED(dGeral[Acronym 2]),
            CALCULATE(
                JenkinsMedianTotal,
                dGeral[Acronym 2] IN VALUES(dGeral[Acronym 2])
            ),
            BLANK()
        )
   
    // CONDITIONAL
    VAR JenkinsResult =
        IF(
            ISFILTERED(dGeral[Acronym 2]),
            IF(
                ISBLANK(FilteredJenkinsMedian),
                (JenkinsMedianTotalFixed / AzureMedianTotalFixed) * AzureMedianAcronym,
                FilteredJenkinsMedian
            ),
            JenkinsMedianTotal
        )
 
    VAR Platform = SELECTEDVALUE(dGeral[Platform])
 
    // SWITCH
        VAR FinalResult =
        SWITCH(
            Platform,
            "Azure", AzureMedianTotal,
            "Jenkins", JenkinsResult
        )
 
RETURN
    FinalResult