Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Fact to Dimension unique value

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 Stat...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

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

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
4 years ago

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