Forum Discussion

lcasey's avatar
lcasey
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

Why wont the same formula work?

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 duplic...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
lcasey
Icon for Post Prodigy rankPost Prodigy
9 years ago

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