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

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

4 Replies

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

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