Fact to Dimension unique value

[Anonymous]

Dear Friends,

i have fact and dimension tables with more than thousands of rows as shown:

 

 i want to get Status data in dimension table from fact table in which  MR have only Status="Initial" else empty

for example in fact table: 1317328 & 1321621 have multiple rows but only status="Initial" so the output for dimension table like be:

  Could you please help me, how can i achieve this

Thanks

[ryan_mayu]

@Anonymous 

I saw you have different "Initials", you need to unify the letter. maybe in PQ, capitalize each word.

Then create a column in table2

Column = 
if (NOT(ISBLANK(MAXX(FILTER('Table','Table'[MR]='Table (2)'[MR NUMBER] &&'Table'[Capitalize Each Word]<>"Initial"),'Table'[MR]))),BLANK(),"Initial")

[amitchandak]

@Anonymous a, new column in dimension table

New column =
var _cnt = countx(filter(fact, Fact[MR Number] = dim[MR Number] && fact[Status ] ="Initial"), Fact[MR Number] )
return
if(isblank(_cnt), blank(), "Initial")

[Anonymous]

Dear Sir

i tried with your dax but MR Number are also appearing where Status="Initial" or "Other value".

Output with your dax: 

Actually i am looking for those MR which  have only Status="Initial"

Requiement Output like:

Thanks

[amitchandak · Accepted solution]

@Anonymous , Try like

New column =
var _cnt = countx(filter(fact, Fact[MR Number] = dim[MR Number] && fact[Status ] ="Initial"), Fact[MR Number] )
var _cnt1 = countx(filter(fact, Fact[MR Number] = dim[MR Number] ), Fact[MR Number] )
return
if(not(isblank(_cnt)) && _cnt = _cnt1, "Initial", blank())