Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Removing Empty Row From Calculated Table

Dear Community, 

 

Hope this message finds you well and healthy!

 

I would like to have a dimension table based on 1 column in my fact table as shown below:

 

The deal is i know there are empty cells in that column, but i don't need them. 

My DAX formula seems to not work as well. 

Can you help me please to get rid of the empty values in this calculated table?

 

All due respect, 

 

Atanas

Need Help
Show and Tell
  • themistoklis's avatar
    themistoklis
    5 years ago

    Anonymous 

     

    Please try any of the options below:

     

    Option 1:
FILTER( SUMMARIZE('America', 'America'[Risk Code]), 'America'[Risk Code]<>BLANK() )

Option 2:
FILTER( DISTINCT('America'[Risk Code]), 'America'[Risk Code]<>BLANK() )

Option 3:
CALCULATETABLE( DISTINCT('America'[Risk Code]), 'America'[Risk Code]<>BLANK() )

Option 4:
Filter (SUMMARIZE ( ​America, America[Risk Code] ) , NOT ( ISBLANK ( [Risk Code] ) ) )

     

4 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    5 years ago

    Anonymous 

     

    Please try any of the options below:

     

    Option 1:
FILTER( SUMMARIZE('America', 'America'[Risk Code]), 'America'[Risk Code]<>BLANK() )

Option 2:
FILTER( DISTINCT('America'[Risk Code]), 'America'[Risk Code]<>BLANK() )

Option 3:
CALCULATETABLE( DISTINCT('America'[Risk Code]), 'America'[Risk Code]<>BLANK() )

Option 4:
Filter (SUMMARIZE ( ​America, America[Risk Code] ) , NOT ( ISBLANK ( [Risk Code] ) ) )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      themistoklis 

       

      Thank you! All of them worked!

       

      I chose that one as i don't want to use iterators:

       

      RiskcodeDim =
      CALCULATETABLE(
      DISTINCT(
      America[Risk Code]
      ),
      America[Risk Code]
      <> BLANK()
      )
       
      God bless you! You and AllisonKennedy 
       
      Thank you both for your help!
       
      Take care!
       
      Atanas
  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago
    Your blank row is probably
    =""
    rather than being actually blank. You can try adding a FILTER( table, column <>"") as well to your DAX, though I would suggest creating your Dimension tables in Power Query.
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      AllisonKennedy , 

       

      Thank you very much for your swift reply, but when i added it i get the following table, which is kinda different:

       

      Also, i agree the dimension tables must be created in Power Query, but i really like to try and do it with DAX for the sake of this task, 

       

      Atanas