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

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!

Need Help

Anonymous
5 years ago
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!

6 Replies

DataInsights

Super User

5 years ago

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

Not applicable

5 years ago

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

Super User

5 years ago

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

Forum Discussion

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

6 Replies

Related content

column deleted but column exists

Missing Column that Doesn't Exist

Return Column Name if Zero Value

Creating custom columns keeps returning a structured Table column

Create measure to return value from column when condition met for two other columns