Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
Hi @lcasey,
Dose Steve_Wheeler's soution works on your side?
Regards,
Xiaoxin Sheng
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]) )
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]) )
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Thanks, That does make sense now, I need to enter a Single formula to get Countries and Regions.
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
User | Count |
---|---|
99 | |
90 | |
83 | |
72 | |
66 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |