In a new column, return value from existing column based on subset of table and a MAX date

[Anonymous]

Let me start by saying, I'm sure there are easierr ways to go about doing this. But I wouldn't be here if things could be done the easy way 😊. 

My manager prefers that report users be able to use the "Show data point as a table" feature when viewing additional details about the data shown in a Visual. Of course, this means (at least, to my understanding) that I can't use a Measure to show Values in a Visual. It also means I can't use Drill Through or Tooltip Pages. Only "Show data point as a table". So I often need to create columns to show data in a manner such as this: 

Black and White

Color Coded

The images above are replications of the data I have in Power BI, formatted in Excel to help with understanding. They're also the same information but one is black and white and one is color coded to illustrate the relationships of the fields. 

The issue I am trying to solve is that I need to write a DAX formula for a new column that produces the information in column J, here. Column J only exists in Excel and not yet in Power BI. The problem is stated as: A Client has many Entire Visits. Within one Entire Visit a Client has multiple Movements as well as a summarizing Entire Visit. Return the last Movement (MAX movement date of corresponding Entire Visit) on the same row that the EOC (summarizing Entire Visit) is on.

I have tried to use LOOKUPVALUE and incorporate some filters, but I haven't been able to get close to an answer. I have a feeling that KEEPFILTERS might be involved, but I'm not as well versed in that filter. 

Any help is appreciated!

[DataInsights]

Hi @Anonymous,

Try this measure:

Last Visit Movement = 
VAR vClient =
    MAX ( ClientVisits[Client ID] )
VAR vClientMovements =
    FILTER (
        ALLSELECTED ( ClientVisits ),
        ClientVisits[Client ID] = vClient
            && CONTAINSSTRING ( ClientVisits[gem_ID], "Movement" )
    )
VAR vMaxDate =
    CALCULATE ( MAX ( ClientVisits[Date] ), vClientMovements )
VAR vLastMovement =
    CALCULATE (
        MAX ( ClientVisits[Length of Stay] ),
        vClientMovements,
        ClientVisits[Date] = vMaxDate
    )
VAR vResult =
    IF (
        CONTAINSSTRING ( MAX ( ClientVisits[gem_ID] ), "Entire Visit" ),
        vLastMovement
    )
RETURN
    vResult

[Anonymous]

Hi @DataInsights ,

I tried to alter the formula you provided to fit the context of my real Power BI information, but the result column still shows up as blank. Below is a capture of my real data and the formula I used.

Last Movement LOS = 
VAR vClient =
    MAX('Gem Fact'[Client ID])
VAR vTreatmentMovements =
    FILTER(
        ALLSELECTED('Gem Fact'),
        'Gem Fact'[Client ID] = vClient
            && CONTAINSSTRING('Gem Fact'[gem_id], "al.")
    )
VAR vMaxDate =
    CALCULATE(MAX('Gem Fact'[Date]),vTreatmentMovements)
VAR vLastMovement =
    CALCULATE(
        MAX('Gem Fact Treatment'[Length of Stay]),
        vTreatmentMovements,
        'Gem Fact'[Date] = vMaxDate
    )
VAR vResult =
    IF(
        CONTAINSSTRING(MAX('Gem Fact'[gem_id_eoc]), "en."),
        vLastMovement
    )
RETURN
    vResult

Power BI Real Data

I hope this clarifies things. Let me know if I need to make anything else more clear.

Thanks!

[DataInsights]

@Anonymous,

Check the column name in the variable vResult (use gem_id, not gem_id_eoc). You may be able to simplify the DAX by using the column gem_id_source:

Last Movement LOS =
VAR vClient =
    MAX ( 'Gem Fact'[Client ID] )
VAR vTreatmentMovements =
    FILTER (
        ALLSELECTED ( 'Gem Fact' ),
        'Gem Fact'[Client ID] = vClient
            && 'Gem Fact'[gem_id_source] = "atl_id"
    )
VAR vMaxDate =
    CALCULATE ( MAX ( 'Gem Fact'[Date] ), vTreatmentMovements )
VAR vLastMovement =
    CALCULATE (
        MAX ( 'Gem Fact Treatment'[Length of Stay] ),
        vTreatmentMovements,
        'Gem Fact'[Date] = vMaxDate
    )
VAR vResult =
    IF ( MAX ( 'Gem Fact'[gem_id_source], "Encounter" ), vLastMovement )
RETURN
    vResult

[Anonymous · Accepted solution]

@DataInsights ,

Thanks so much for your help!

With some additional tweaks, I was able to come up with the formula I needed and so far, it works as expected:

Last Treatment LOS = 
VAR vClient =
    'Gem Fact'[Client ID]
VAR vClientEncounters =
    'Gem Fact'[usr_field2]
VAR vTreatmentMovements =
    FILTER(
        ALLSELECTED('Gem Fact'),
        'Gem Fact'[Client ID] = vClient
            && 'Gem Fact'[usr_field2] = vClientEncounters
                && 'Gem Fact'[LOS Calculation] = "Treatment"
    )
VAR vMaxDate =
    CALCULATE(MAX('Gem Fact'[Date]), vTreatmentMovements)
VAR vLastTreatment =
    CALCULATE(MAX('Gem Fact'[Length of Stay]), vTreatmentMovements, 'Gem Fact'[Date] = vMaxDate)
VAR vResult =
    IF('Gem Fact'[LOS Calculation] = "EOC",
        vLastTreatment)
RETURN
   vResult

One issue was that I was using this formula for a New Column, so either I should've written this as a measure and used a New Column to call it, or something was misunderstood. 

Another issue was that I realized the formula didn't consider a Client ID who had more than one EOC which would mean that more than one unique Treatment LOS would be needed (which is obviously due to a deeper understanding of the data itself on my part, not a malicious comment, I don't see how you would've been able to see that possibility anyway). So, using your model, I was able to incorporate that successfully.

I honestly don't use this variable method a lot, so thank you for getting me to practice it. I think I'll use it more in the future!

[v-robertq-msft]

Hi, @Anonymous 

Glad to hear that you have solved your problem with the help of DataInsights.

Would you like to mark his reply or your own reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@Anonymous,

Glad to hear you were able to tweak the DAX to work as expected. Keep in mind that a calculated column isn't affected by user selections (e.g., slicer, filter), so ALLSELECTED isn't needed.

Variables make coding and understanding DAX easier, and also facilitate troubleshooting. It's a good practice to implement.

Best of luck!