DAX help

[audstrich]

Hi everyone,

I would like to get one of three outputs from the expression that I am hoping someone can teach me about: trial, implant, or battery replacement. Two of these outputs are very simple, one code relates to each. But the "implant" output will need to see if both codes were used under the same ID number.

63650 alone = trial

63685 alone = battery replacement

63650 & 63685 = implant

ID Number	Code	Output
1	63650	trial
2	63685	battery replacement
3	63650	implant
3	63685	implant

The data has only one code per row and each row has an ID number. The column should return "implant" if there are two rows with the same ID number, one containing the code 63650 and the other containing 63685.

There are other codes used in the table but I only care about an output for these three instances.

Thank you!! I'm sure this isn't very complicated but I'm no expert and I very much so appreciate the help.

Audrey

[parry2k]

@audstrich your question is not fully clear, what is the logic to get output "Trial" or "battery replacement" , although is clear if there is more than one code under same id then it will be "Implant" but not sure what is the logic in first two cases?

[audstrich]

@parry2k trial or battery replacement is used when for that ID number there is only the one code, or the only other instances for that ID are other codes not included in the table (99124, J1100, etc.). If there is only 63650 for that ID and no 63685 as well for that ID, then it is a trial. If only 63685 and no 63650 for that ID, it was a battery replacement. The ID number is the unique value assigned to a procedure that could include many codes or just one.

I'm sure there is a much shorter version to explain this in logical terms, I'm sorry!

[parry2k · Accepted solution]

@audstrich add new column in your table using following expression

Output 2 = 
VAR __data = CALCULATETABLE( VALUES(Table8[Code]), ALLEXCEPT( Table8, Table8[ID Number] ) )
RETURN
IF( Table8[Code] IN {"63650","63685" },
    SWITCH( TRUE(),
        "63650" IN __data  &&  NOT "63685"  IN __data , "Trial",
        "63685" IN __data  &&  NOT "63650"  IN __data , "Battery Replacement",
        "63685" IN __data  &&      "63650"  IN __data , "Implant"
    )
)

[audstrich]

A single value for column 'CPT Code' in table 'Master 37105' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[parry2k]

Are you adding a measure or column?

[audstrich]

Column.

[parry2k]

Hmmm, not sure why it will give an error message when it is column, can you test it on sample dataset you provided, it all worked fine for me

[audstrich]

The Home Table was wrong! It works beautifully now. Thank you so much!! I really appreciate it.

[parry2k]

Woo hoo, glad to hear.

[audstrich]

Thank you so much! But it's still not working. I get this error:

A single value for column 'Code' in table 'Table 8' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

When I add COUNT to the formula, I get this error:

Function 'CONTAINSROW' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

[parry2k]

@audstrich I'm not sure what you mean by count in the formula, first add this column and then you can add measure to count different output.