Forum Discussion

willit6182's avatar
willit6182
Frequent Visitor
2 years ago
Solved

Simple DAX expression help?

Relatively new Power BI user here. Working on some DAX language I can't figure out.
 
Scenario is as follows: Patients are seen by doctors at specific encounters. Each encounter has multiple charges attached called CPT codes. Each CPT code has a specific RVU value assigned to it. Each CPT code is already grouped into 1 of 14 different Categorical descriptors.
 
Each Encounter has multiple CPT codes
Each CPT code is seen in multiple Encounters
 
 
Data modeling is thus:
 
Table 1 (Encounters)
- EncounterID [text, unique & distinct]
 
Table 2 (CPTs)
- Procedure Code [text, unique & distinct]
- Procedure Description (groups) [191 codes into 14 groups]
- RVUs [number]
 
Table 3 (bridge table)
- EncounterID (multiple instances)
- Procedure Code (multiple instances)
 
Table 1 --> Table 3 via Encounter ID with 1 -> many, uni-directional filter
Table 2 --> Table 3 via Procedure Code with 1 -> many, bi-directional filter
 
Problem: I am trying to create a new column in Table 1 (Encounters) that shows only the Procedure Description (group) of just the highest RVU Procedure code for that encounter.
 
Any ideas on the DAX expression to define the new column?
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    willit6182 Not 100% sure I understand this. Sample data would help but perhaps something like this:

    Column =
  VAR __ID = [EncounterID]
  VAR __Codes = SELECTCOLUMNS( FILTER( 'Table 3', [EncounterID] = __ID ), [Procedure Code] )
  VAR __MaxRVU = MAXX( FILTER( 'Table 2', [Procedure Code] IN __Codes ), [RVU] )
  VAR __Result = MAXX( FILTER( 'Table 2', [Procedure Code] IN __Codes && [RVU] = __RVU ), [Procedure Description (groups) )
RETURN
  __Result

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    willit6182 Not 100% sure I understand this. Sample data would help but perhaps something like this:

    Column =
  VAR __ID = [EncounterID]
  VAR __Codes = SELECTCOLUMNS( FILTER( 'Table 3', [EncounterID] = __ID ), [Procedure Code] )
  VAR __MaxRVU = MAXX( FILTER( 'Table 2', [Procedure Code] IN __Codes ), [RVU] )
  VAR __Result = MAXX( FILTER( 'Table 2', [Procedure Code] IN __Codes && [RVU] = __RVU ), [Procedure Description (groups) )
RETURN
  __Result
    • willit6182's avatar
      willit6182
      Frequent Visitor
      2 years ago

      Thanks but I'm still not quite getting it to work.

       

      To simply the dataset, I now have one larger table as below. This has multiple instances of the Encounter ID, each with a distinct Procedure Code.

       

      EncounterIDProcedure CodeRVU Value
      102247204202252.45
      10224720477012261.5
      104241596382221.44
      104241596991520.25
      10424159677012261.5
      110248074365903.1
      110248074991520.25
      11024807477001260.38
      12324346276604260.59
      129245185325552.27
      1012415987325552.27
      1022429872494064
      1022429872991520.25
      1022445014507063.8
      1022445014991520.25
      1022445014504311.1
      1022445014503891.1

       

      I also have a second KEY table that lists every possible Procedure Code only once, with a corresponding Category as below.

       

      Procedure CodeCategory
      10005Minors
      10006Minors
      10030Visceral Tubes
      20206Biopsy
      20220Biopsy
      20225Biopsy
      20501Fluoro
      20552Fluoro
      22510Kyphoplasty
      22513Kyphoplasty
      22514Kyphoplasty
      22515Kyphoplasty
      32408Biopsy
      32550Visceral Tubes
      36010Catheter, Angio, Guidance, Sedation
      36012Catheter, Angio, Guidance, Sedation

       

      I am trying to create a measure that classifies each Distinct Encounter ID by the Category of the Procedure Code with the largest RVU value. In the first table above, the highest RVU value procedure code from each Encounter ID is highlighted in Red. So basically I need that Encounter ID to be classified as the Category on the second table linked to the Procedure code in Red.