Simple DAX expression help?

[willit6182]

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?

[Greg_Deckler · Accepted solution]

@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]

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.

EncounterID	Procedure Code	RVU Value
102247204	20225	2.45
102247204	7701226	1.5
104241596	38222	1.44
104241596	99152	0.25
104241596	7701226	1.5
110248074	36590	3.1
110248074	99152	0.25
110248074	7700126	0.38
123243462	7660426	0.59
129245185	32555	2.27
1012415987	32555	2.27
1022429872	49406	4
1022429872	99152	0.25
1022445014	50706	3.8
1022445014	99152	0.25
1022445014	50431	1.1
1022445014	50389	1.1

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

Procedure Code	Category
10005	Minors
10006	Minors
10030	Visceral Tubes
20206	Biopsy
20220	Biopsy
20225	Biopsy
20501	Fluoro
20552	Fluoro
22510	Kyphoplasty
22513	Kyphoplasty
22514	Kyphoplasty
22515	Kyphoplasty
32408	Biopsy
32550	Visceral Tubes
36010	Catheter, Angio, Guidance, Sedation
36012	Catheter, 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.