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

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

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    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())