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.
I have a data file, that has two main columns: source and reach.
I want to know the total reach value for for all of the distict source. So basiclly, remove all the duplicate source, and only keep one record, and then calculate the total value.
how to do that?
Solved! Go to Solution.
I find the right solution now!
SUMX(DISTINCT(expedia_top_us_sources_export[Source]), FIRSTNONBLANK(expedia_top_us_sources_export[Reach], 0))
Hey,
basically it's not clear what you want:
Personally I would solve this by changing my data model, create a new table (Source) from within Power Query that references your data file, then remove all the other columns and all the duplicates, and then create a relationship between the new table (on the one side) and your data file (on the many side) using the column Source. This assumes, that there is 1 to 1 relationship between the columns Source and Reach.
Finally, delete the column "Reach" from your data file.
Hopefully this gives you an idea.
Regards,
Tom
I find the right solution now!
SUMX(DISTINCT(expedia_top_us_sources_export[Source]), FIRSTNONBLANK(expedia_top_us_sources_export[Reach], 0))
Hey Robert,
additionally you also find this blog post interesting, it's about the differences between DISTINCT and VALUES:
Regards
Tom
accutually, i just use the power bi. I used to use the tableau, and tableau can use {INCLUDE [Source]: MAX([Reach])} to achieve the goal. For this expression, it will just look at the max reach for each source to remove the duplicate. So is there any function in power can have the same thing?
Tried power query, all of my other graph will update for the new data. So it not work quite well for this case.
Thanks
Hey,
it's also possible to achieve the same in Power BI by creating a calculated column, here is the DAX statement:
MAX Source = CALCULATE( MAX('Table1'[Reach]) ,ALLEXCEPT('Table1',Table1[Source]) )
In addition to this you may also have to change the default summerization for the column, mark the new column and switch to the modeling menu:
In addition you also have to consider what your expected result is for the total of all sources.
Regards
Tom
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |