Why wont the same formula work?

[lcasey]

Hello,

I have a table I created called countries, it is a simple formula.

Countries = Distinct('Transactions'[COUNTRY])

This works perfect--No issues and yes there are multiple AND duplicate country values.

Region Slicer = DISTINCT(Transactions[Region]) Does Not work In the SAME table. Why do two same exact formulas have different results?

I do not want to creat yet a whole other table called Regions. Shouldnt I simply be able to add  regions to the Country Table?

[Anonymous]

Hi @lcasey,

Dose Steve_Wheeler's soution works on your side?

Regards,

Xiaoxin Sheng

[Anonymous]

Your "Country Formula" is a Table, and the "Region Formula" is a measure.

Try change your "Distinct Countries Table Formula" to this:

Countries = Distinct( SELECTCOLUMNS(Transactions;"Country Slicer";[Country];"Region Slicer";[Region]) )

[lcasey]

Hello,

This worked as expected, But I am still getting a relation error due to there being Blanks in the data, Unfortunatly, I cant change the dataset and the dataset contains years of previous mispellings etc.  The Current Data though is always correctly entered.

Is there a way to filter out Blank Countries as I need to relate tables based on countries?  I am trying to Avoid creating a manual table that needs to be updated. 

Countries = Distinct( SELECTCOLUMNS(Transactions,"Country Slicer",[Country],"Region Slicer",[Region]) )

[KHorseman]

Oops, yeah that's a measure not a column. I mixed up the icons in my head. Still, either way it's not a table.

[KHorseman]

It's because you're not creating a new table there. According to your screenshot, you're creating a new column in an existing table (Countries). That's a table formula, not a column formula.

[lcasey]

Thanks, That does make sense now, I need to enter a Single formula to get Countries and Regions. 

[Anonymous · Accepted solution]

You could do it in Power Query with something like this:

let
    Source = Transactions,
    KeptOnlyCountryAndRegion = Table.SelectColumns(Source,{"Country", "Region"}),
    RemovedBlankCountries = Table.SelectRows(KeptOnlyCountryAndRegion, each [Country] <> ""),
    RemovedDuplicateCountryandRegions = Table.Distinct(RemovedBlankCountries)
in
    RemovedDuplicateCountryandRegions