Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
80 | |
67 |