How To Get Exact Value and Not To Use First or Last

[tksnota]

Hi,

Can I create a measure and not use First or Last as shown because several data have the same numbers but the descriptions varies.

Regards,

Noel

[tksnota · Accepted solution]

thanks for the help but what i really need is the exact value which should only one based on the table not using first or last... can it be done?

[v-heq-msft · Accepted solution]

Hi @tksnota ，
Thanks to @rajendraongole1  reply.
Here is what i wang to add:
Sample Data:

There are two ways to get exact data
First, you can limit them based on their different metrics, using the calculate function with the filter function to filter the table down to one piece of data, and then using the aggregate function to get the
Creating a new dimension table based on a conditional column

Table 2 = VALUES('Table')

Use this column as slicer and create a measure

Selected Email = 
CALCULATE(
    MAX('Table'[Contact Email]),
    FILTER(
        'Table',
        'Table'[Contact No.] = SELECTEDVALUE('Table 2'[Contact No.]) &&
        'Table'[Last Description] = SELECTEDVALUE('Table 2'[Last Description])
    )
)

Final output

The second method is to create a unique primary key for identification, and the following is to use rank to create this unique primary key

RANK = 
RANKX(
    'Table',
    'Table'[Contact Name],
    ,ASC,Skip
)

Selected Discrption = 
CALCULATE(
    MAX('Table'[Last Description]),
    FILTER(
        'Table',
        'Table'[RANK] = SELECTEDVALUE('Table 3'[RANK])
    )
)

Best regards,
Albert He

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

[tksnota · Accepted solution]

thanks for the help but what i really need is the exact value which should only one based on the table not using first or last... can it be done?

[rajendraongole1]

Hi @tksnota - you can use a function in measure with Hasonvalue() function in dax measure if you want to display all contacts.

For example i have used country change it to as per your requirement.

Country =

IF(

    HASONEVALUE(Financials[Country]),

    VALUES(Financials[Country]),

    "Multiple Countries"

)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!