Removing Empty Row From Calculated Table

[Anonymous]

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

[themistoklis · Accepted solution]

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

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

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]

@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