Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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?

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ，
    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



3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Anonymous - 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!!

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ，
    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