Forum Discussion

audstrich's avatar
audstrich
Regular Visitor
7 years ago
Solved

DAX help

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...
  • parry2k's avatar
    parry2k
    7 years ago

    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's avatar
parry2k
Super User
7 years ago

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's avatar
audstrich
Regular Visitor
7 years ago

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's avatar
    parry2k
    Super User
    7 years ago

    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's avatar
      audstrich
      Regular Visitor
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

        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.

         

         

    • audstrich's avatar
      audstrich
      Regular Visitor
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

        Are you adding a measure or column?