Show the column information based on a mesure

[fbittencourt]

Hi all,

I have the combination of two mesures that retrives the latest upd value from a column:

Cost Recent Upd =

 CALCULATE(

    [Total  IT Cost],

    FILTER('IT Cost (KCar)','IT Cost (KCar)'[Wiser Date]=[Date Most Recent Upd]

 ))

Date Most Recent Upd =

CALCULATE(

    MAX('IT Cost (KCar)'[Wiser Date]),

    FILTER(

        'IT Cost (KCar)',

        CONTAINSSTRING('IT Cost (KCar)'[Budget Phase], "Upd")

    )

)

On the last mesure we filter " Upd" as CONTAINSTRING, and we retrieved the result, but on my column I have 5 differents Upd : 1Upd, 2Upd, 3Upd, 4Upd and 5 Upd.

How can I show the information from the Upd? Now I have the result only

Tks! All in advance!!

[Anonymous]

Hi @fbittencourt ,

Thanks for the reply from bhanu_gautam .

Do you want to retrieve the Value and Date pairs corresponding to the latest upd value from the column? Since I don't know how your “Total IT Cost” measure is calculated and I don't have example data, in my test I get the value and date of the latest upd value and replace it with your [Total IT Cost] measure when you use it.

Here is my example data:

Modify your two measures as follows:

Date Most Recent Upd = 
CALCULATE(
    MAX('IT Cost (KCar)'[Wiser Date]),
    FILTER(
        'IT Cost (KCar)',
        SEARCH("Upd", 'IT Cost (KCar)'[Budget Phase], 1, 0) > 0
    )
)

Cost Recent Upd = 
CALCULATE(
    MAX('IT Cost (KCar)'[Value]),
    FILTER(
        'IT Cost (KCar)',
        'IT Cost (KCar)'[Wiser Date] = [Date Most Recent Upd]
    )
)

Since you mentioned that you have more than one upd, you can use the SEARCH () function to detect whether the string contains the specified substring.

The final page visualization is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[fbittencourt]

Hi Yang;

Thanks for the work of creating pbi file. I did not mention very well my need.

Let me try to explain, I just want to know how we can retrieve information from the number of last update on a title or in a card, just because we have the value as example 330 in the card, we know that is last update and must show the info from the update on this case: 5 Upd

Tks again!!

[Anonymous · Accepted solution]

Hi @fbittencourt ,

You not only want to get the value and date of the latest upd, but you also want to display the latest upd, right?

Change the Cost Recent Upd String measure as follows:

Cost Recent Upd String = 
VAR _LastValue =
    CALCULATE(
        MAX('IT Cost (KCar)'[Value]),
        FILTER(
            'IT Cost (KCar)',
            'IT Cost (KCar)'[Wiser Date] = [Date Most Recent Upd]
        )
    )
VAR _LastUpd =
    CALCULATE(
        MAX('IT Cost (KCar)'[Budget Phase]),
        FILTER(
            'IT Cost (KCar)',
            'IT Cost (KCar)'[Wiser Date] = [Date Most Recent Upd]
        )
    )
RETURN 
    FORMAT(_LastValue, "0") & "-" & _LastUpd

Create a new measure:

Upd Date = 
VAR _LastUpd =
    CALCULATE(
        MAX('IT Cost (KCar)'[Budget Phase]),
        FILTER(
            'IT Cost (KCar)',
            'IT Cost (KCar)'[Wiser Date] = [Date Most Recent Upd]
        )
    )
RETURN 
    [Date Most Recent Upd] & "-" & _LastUpd

The final visual effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[bhanu_gautam]

@fbittencourt Modify this measure to filter for each specific "Upd" value.

DAX
Date Most Recent Upd =
CALCULATE(
MAX('IT Cost (KCar)'[Wiser Date]),
FILTER(
'IT Cost (KCar)',
CONTAINSSTRING('IT Cost (KCar)'[Budget Phase], "1Upd") ||
CONTAINSSTRING('IT Cost (KCar)'[Budget Phase], "2Upd") ||
CONTAINSSTRING('IT Cost (KCar)'[Budget Phase], "3Upd") ||
CONTAINSSTRING('IT Cost (KCar)'[Budget Phase], "4Upd") ||
CONTAINSSTRING('IT Cost (KCar)'[Budget Phase], "5Upd")
)
)

DAX
Cost Recent Upd =
CALCULATE(
[Total IT Cost],
FILTER('IT Cost (KCar)', 'IT Cost (KCar)'[Wiser Date] = [Date Most Recent Upd])
)