Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tksnota
Helper III
Helper III

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

 

tksnota_0-1718266517496.png

 

2 ACCEPTED SOLUTIONS
tksnota
Helper III
Helper III

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?

 

View solution in original post

v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1718347300481.png

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

vheqmsft_1-1718348752702.png

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])
    )
)

 

vheqmsft_2-1718349015629.png

 

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



View solution in original post

3 REPLIES 3
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1718347300481.png

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

vheqmsft_1-1718348752702.png

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])
    )
)

 

vheqmsft_2-1718349015629.png

 

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
Helper III
Helper III

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
Community Champion
Community Champion

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"
)

 

rajendraongole1_1-1718271040318.png

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

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.