Forum Discussion

lcasey's avatar
lcasey
Post 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 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?

 

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

     

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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]) )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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.

    • lcasey's avatar
      lcasey
      Post 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]) )

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi lcasey,

     

    Dose Steve_Wheeler's soution works on your side?

     

    Regards,

    Xiaoxin Sheng