Using What-if parameter for a Custom column written with DAX for True or False (Example in Post)

[jbinder4]

Hello, 

I am trying to replace nested if statements that evaluated hardwired numbers in DAX with what-if parameter values that allow users to change the number that is being evaluated in the DAX statement. 

I have been able to filter the values report wide, but even so I am not getting the output desired because I don't believe it is being evaluated the way I intended. 

Essentially my mission is to have users be able to set the time they wish for their own personalized SLA that will give out True or False for each row. 

Any help on this would be so greatly appreciated. Let me know if there is any further questions you may have for me to help clarify on. 

Thank you!!!

[v-jingzhang]

Hi @jbinder4 

Once you have solved this problem, can you share the measure code here to help other users in the future?

I think you can first modify your column code into below. 

column =
VAR __completedInMinute = DATEDIFF ( TicketInfo[CreatedDateTime], TicketInfo[CompletedDateTime], MINUTE )
VAR __respondedInMinute = DATEDIFF ( TicketInfo[CreatedDateTime], TicketInfo[RespondedDateTime], MINUTE )
VAR __completedInHours = DATEDIFF ( TicketInfo[CreatedDateTime], TicketInfo[CompletedDateTime], HOUR )
VAR __respondedInHours = DATEDIFF ( TicketInfo[CreatedDateTime], TicketInfo[CompletedDateTime], HOUR )
RETURN
    SWITCH (
        TicketInfo[PriorityName],
        "1- Urgent",
            IF (
                __completedInMinute <= SLA_UrgentCompleted[UrgentCompleted Value]
                    && __respondedInMinute <= SLA_UrgentResponded[SLA_UrgentRequested Value],
                TRUE (),
                FALSE ()
            ),
        "2- High",
            IF (
                __completedInHours <= SLA_HighCompleted[SLA_HighCompleted Value]
                    && __respondedInMinute <= SLA_HighResponded[SLA_HighResponded Value],
                TRUE (),
                FALSE ()
            ),
        "3- Medium",
            IF (
                __completedInHours <= SLA_MediumCompleted[SLA_MediumCompleted Value]
                    && __respondedInHours <= SLA_MediumResponded[SLA_MediumResponded Value],
                TRUE (),
                FALSE ()
            ),
        "4- Normal",
            IF (
                __completedInHours <= SLA_NormalCompleted[SLA_NormalCompleted Value]
                    && __respondedInHours <= SLA_NormalResponded[SLA_NormalResponded Value],
                TRUE (),
                FALSE ()
            ),
        "5- Low",
            IF (
                __completedInHours <= SLA_LowCompleted[SLA_LowCompleted Value]
                    && __respondedInHours <= SLA_LowResponded[SLA_LowResponded Value],
                TRUE (),
                FALSE ()
            ),
        FALSE ()
    )

Paste it into your table to check whether it gets the same result as your original code. If result is the same, you can modify it into a measure. Just add selectedvalue() function to wrap the columns. For example, 

VAR __completedInMinute = DATEDIFF ( SELECTEDVALUE ( TicketInfo[CreatedDateTime] ), SELECTEDVALUE ( TicketInfo[CompletedDateTime] ), MINUTE )

SWITCH (
        SELECTEDVALUE ( TicketInfo[PriorityName] ),
        ......................

Kindly let me know if this helps.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[jbinder4]

@Greg_Deckler Thank you so much for a quick reply! I am understanding that a column won't work, so I will have to somehow make this work through a measure. I am a bit unsure of how to go about that as of right now. 

Here is the DAX I have written for the Customized Column that I now need to make into a measure. The purpose of this is to evaluate whether or not each Ticket that comes into our CRM meets the SLA (Service-level agreement) time from our technicians: 

-----

metsla10 =

    IF( AND(TicketInfo[PriorityName]="1- Urgent", AND(DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[CompletedDateTime], MINUTE)<=SLA_UrgentCompleted[UrgentCompleted Value], DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[RespondedDateTime], MINUTE)<=SLA_UrgentResponded[SLA_UrgentRequested Value])),

        TRUE(),

    IF( AND(TicketInfo[PriorityName]="2- High", AND(DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[CompletedDateTime], HOUR)<=SLA_HighCompleted[SLA_HighCompleted Value], DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[RespondedDateTime], MINUTE)<=SLA_HighResponded[SLA_HighResponded Value])),

        TRUE(),

    IF(AND(TicketInfo[PriorityName]="3- Medium", AND(DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[CompletedDateTime], HOUR)<=SLA_MediumCompleted[SLA_MediumCompleted Value], DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[RespondedDateTime], HOUR)<=SLA_MediumResponded[SLA_MediumResponded Value])),

        TRUE(),

    IF( AND(TicketInfo[PriorityName]="4- Normal", AND(DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[CompletedDateTime], HOUR)<=SLA_NormalCompleted[SLA_NormalCompleted Value], DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[RespondedDateTime], HOUR)<=SLA_NormalResponded[SLA_NormalResponded Value])),

        TRUE(),

    IF(AND(TicketInfo[PriorityName]="5- Low", AND(DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[CompletedDateTime], HOUR)<=SLA_LowCompleted[SLA_LowCompleted Value], DATEDIFF(TicketInfo[CreatedDateTime], TicketInfo[RespondedDateTime], HOUR)<=SLA_LowResponded[SLA_LowResponded Value])),

        TRUE(),

    FALSE())))))

-------

Thank you once again and please let me know if there is anything more I can do to help!

[Greg_Deckler]

@jbinder4 Step 1. Use a SWITCH(TRUE()...) statement instead of nested IF statements. Will clean up the code tremendously!

Step 2. Create a measure using the same code as the calculated column but use aggregations whenever you refer to a column. Probably can use MAX for the most part. like instead of TicketInfo[PriorityName] = "1-Urgent" it would be MAX(TicketInfo[PriorityName]) = "1-Urgent".

Step 3. Create a table visualization that essentially has the same layout as your base table or otherwise make sure that each row is unique in the table visualization the same way each row appears in your data table. Add your measure to this table visual.

This is based upon what I *think* you are trying to accomplish. I may be incorrect in some assumption.

[jbinder4]

@Greg_Deckler Hello, 

Thank you so much for your help thus far! I have went through and tested and tampered with your different steps you layed out for me in your last post. I have had a bit of trouble getting the correct output for the measure when I put it in a table visualization. I also had trouble understanding how to correctly implement the SWITCH(TRUE() functions to replace the nested IF statements. 

This is completely far too much to ask, but is there any way I could set up a half hour meeting with you via MS teams to share screen and walk through some ideas? I have ran out of resources within my organization and Youtube has been barren for help considering how situational this issue is. 

Let me know if there is any way this could be possible. If not I completely understand, and I am very grateful for the help you've already given me! This lead me to progress a bit with the project and I was stuck in quick sand for a long bit before. 

If you are able to swing something you can reach me at: Jbinde@libbey.com 

Thank you! 

[Greg_Deckler]

@jbinder4 I don't normally do that although I have made rare exceptions. Is there any way that you can post your PBIX or a sample PBIX that emulates the issue you are having? Provides a lot more flexibility on my end to work on things as time permits.

[jbinder4]

@Greg_Deckler Absolutely! Is there anyway I could email it instead of posting it here visibly on this thread? 

[Greg_Deckler]

@jbinder4 Sure, gdeckler@fusionalliance.com

[Greg_Deckler]

@jbinder4 If you are using a What-If parameter, that would be a measure that they would set. That is not going to work dynamically with a calculated column which only calculates during data refresh. You would need a measure to return true/false for every row, not a calculated column, if you want things to be dynamic.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.