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, 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 NumberCodeOutput
163650trial
263685battery replacement
363650implant
363685implant

 

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'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"
    )
)

11 Replies

  • 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"
    )
)