Help with Switch Function

DAX DAX Calculations
1

Hello,

I am trying to create a new column in my data set to filter vales on my table. They look like this below:

image

I first tried to create a table I could use to filter the columns by but I could not figure out how to make the relationships work appropriately. I thought the Switch function might work better but I am having some challenges.

The problem I am having is two-fold. First, it returns the first True value however I need it to tell me if there is more than one of the columns is a True Statment. For example, if Column A value is True then the result would be “Billing Issue”. If Column B value is True then the result would be “Provider Issue”. I need it to somehow delineated both of these so that I can filter by them. Also, I am having some trouble understanding how to utilize the “Else” within the switch statement.

This is an example of one of my Switch Statements:

Billing Issue =

SWITCH( TRUE(),

CIP_DATA[covered_icd_code_required] = TRUE, "Billing Issue",

CIP_DATA[credentialing] = TRUE, "Billing Issue",

CIP_DATA[documentation] = TRUE, "Billing Issue",

CIP_DATA[icd_lcd_ncd] = TRUE, "Billing Issue",

CIP_DATA[lack_of_missing_documentation] = TRUE, "Billing Issue",

CIP_DATA[medical_necessity] = TRUE, "Billing Issue",

CIP_DATA[medical_records_required] = TRUE, "Billing Issue",

CIP_DATA[modifier] = TRUE, "Billing Issue",

CIP_DATA[need_alternative_code] = TRUE, "Billing Issue",

CIP_DATA[need_medical_necessity_letter] = TRUE, "Billing Issue",

CIP_DATA[pre_auth] = TRUE, "Billing Issue",

CIP_DATA[signature] = TRUE, "Billing Issue")

The approach I have taken write now is to create a column for each filter value I want and write a switch function for each column. I don’t like the outcomes though right now and know there must be a better way to do this.

Any help or advice would be greatly appreciated.

2

Matthew,

In you current SWITCH Statement you need to provide an alternate result. Right now your only result is “Billing Issue”. After CIP_DATA[signature] = TRUE, "Billing Issue") you need to add a comma after “Billing Issue” and provide an alternate response ( common one used is BLANK(), but you can put whatever you would like). I would recommend going over Sam’s content on Switch Statements. Here is a link to a video to help you out.

Are you wanting this to be a calculated column, or a measure? The way you have it currently structured, it needs to be a calculated column

Thanks
Jarrett

3

Jarrett,

Thank you. I reviewed the video and adjusted it for the else portion of the switch statement. Do you are anyone else have any thoughts on the other portion of my problem?

The problem I am having is two-fold. First, it returns the first True value however I need it to tell me if there is more than one of the columns is a True Statment. For example, if Column A value is True then the result would be “Billing Issue”. If Column B value is True then the result would be “Provider Issue”. I need it to somehow delineated both of these so that I can filter by them.

4

Matthew,

You can use the or function.

need || after each TRUE.

Would look something like this:

Billing Issue =
SWITCH (
    TRUE (),
    CIP_DATA[covered_icd_code_required] = TRUE
        || CIP_DATA[credentialing] = TRUE
        || CIP_DATA[documentation] = TRUE
        || CIP_DATA[icd_lcd_ncd] = TRUE
        || CIP_DATA[lack_of_missing_documentation] = TRUE
        || CIP_DATA[medical_necessity] = TRUE
        || CIP_DATA[medical_records_required] = TRUE
        || CIP_DATA[modifier] = TRUE
        || CIP_DATA[need_alternative_code] = TRUE
        || CIP_DATA[need_medical_necessity_letter] = TRUE
        || CIP_DATA[pre_auth] = TRUE
        || CIP_DATA[signature] = TRUE, "Billing Issue",
    BLANK ()
)

Or you could use an IF statement if you only have two possible outcomes. That would look something like this:

Billing Issue =
IF (
    CIP_DATA[covered_icd_code_required] = TRUE
        || CIP_DATA[credentialing] = TRUE
        || CIP_DATA[documentation] = TRUE
        || CIP_DATA[icd_lcd_ncd] = TRUE
        || CIP_DATA[lack_of_missing_documentation] = TRUE
        || CIP_DATA[medical_necessity] = TRUE
        || CIP_DATA[medical_records_required] = TRUE
        || CIP_DATA[modifier] = TRUE
        || CIP_DATA[need_alternative_code] = TRUE
        || CIP_DATA[need_medical_necessity_letter] = TRUE
        || CIP_DATA[pre_auth] = TRUE
        || CIP_DATA[signature] = TRUE,
    "Billing Issue",
    BLANK ()
)

Thanks
Jarrett

5

@matthew.wright,

This happens occasionally, where two people jump on the same problem at the same time and come up with entirely different but equally valid solutions, which is one of the things I think makes Power BI fascinating. I took a very different path on this one, focusing on reshaping your data via unpivot:

image
image753×641 26.1 KB

Once the data are reshaped, with one measure to do the heavy lifting (below) and a couple of simple ones to do the conditional formatting:

Problems Experienced = 

VAR CalcProbs =
CALCULATE(
    CONCATENATEX(
        'Data Unpivoted',
        'Data Unpivoted'[Problem],
        UNICHAR( 10 ),
        RELATED( Problems[Problem Category] ),
        ASC
    ),
    FILTER( 
        'Data Unpivoted',
        'Data Unpivoted'[Problem Status] = TRUE()
    )
)

RETURN
COALESCE( CalcProbs, "NO PROBLEMS" )

you can easily analyze/visualize the data in lots of ways that I think will meet your requirements.

image
image850×402 26.7 KB

I hope this is helpful. Full solution file posted below.

6

Hi @matthew.wright, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

7

All,

I appreciate everyone’s help. I worked with both solutions and ended up going with the first solution. Thank you so much for everyone’s help!