Unique value of column based on min of other column

[Anonymous]

In the above figure, pair ID 150-151 & 57-58 are getting repeated as the Development Status is different. Is there a way to show only unique pair ID based on Min(Development Status). So, Pair ID 150-151 should show only Development Status - (1- Roadmap), similarly 57-58 Pair ID should show Development Status - (1- Roadmap). Let me know if there is a way to solve this?

[amitchandak · Accepted solution]

@Anonymous , in-display if all are un summarize and status is the min/first this should happen.

Find attcahed file after signature

Unless you need a measure. There you can use firstnonblankvalue

refer : https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create column to get the Status number, then create measure Filter1  ,  put measure Filter1 in the Visual Level Filter of table visual  , and setting the Filter1 as "is not blank".

Column :
NumberSatus = LEFT(Table1[Development Status],1)



Measure:

Filter1 = var _MinStatus= CALCULATE(MIN(Table1[NumberSatus]), ALLEXCEPT(Table1,Table1[Pair]))

RETURN

IF(MAX(Table1[NumberSatus])= _MinStatus, 1, BLANK())

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

[Anonymous]

Hello @amitchandak  Here you go, sample data. 

Pair	Market Status	Development Status	Source	Target
43-44	2 - Committed to Market	1 - Roadmap	A	B
43-44	2 - Committed to Market	1 - Roadmap	A	B
57-58	3 - Future	6 - Cross Division Tested	B	A
57-58	3 - Future	1 - Roadmap	B	A
115-116	X - Removed from Scope	1 - Roadmap	B	A
115-116	X - Removed from Scope	1 - Roadmap	B	A
150-151	X - Removed from Scope	3 - In Progress	A	B
150-151	X - Removed from Scope	1 - Roadmap	A	B

Output: 

Pair	Market Status	Development Status	Source	Target
43-44	2 - Committed to Market	1 - Roadmap	A	B
57-58	3 - Future	1 - Roadmap	B	A
115-116	X - Removed from Scope	1 - Roadmap	B	A
150-151	X - Removed from Scope	1 - Roadmap	A	B

[amitchandak · Accepted solution]

@Anonymous , in-display if all are un summarize and status is the min/first this should happen.

Find attcahed file after signature

Unless you need a measure. There you can use firstnonblankvalue

refer : https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak