Forum Discussion
DAX help
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" ) )
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!
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"
)
)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.
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.