Forum Discussion
Filtering out duplicates
So, this ought to be easy, but one of the caveats in the online training is that filters are not something a newbie like me will understand first time through.
So here's the setup - I have some facilities. The facilities are in counties, and the counties are in larger regions. Some counties have one facility, some have two or three. I am mapping the number of cases in the area of each facility by using the county total. That's useful for the map...for example, if you look at the tooltip for any one of the three facilities in a county, you will see the county total.
The problem comes when I try to aggregate to show the total by region...It wants to take the county total for each of the three facilities in that county and sum them...I only need one of those three added into the regional total.I don't see a way to attach a file, or I would attach an example file I built.
Hi ripstaur ,
Create a measure like so:
Measure = VAR t = SUMMARIZE ( 'Table', 'Table'[Region], 'Table'[County], "Total_", MAX ( 'Table'[County total cases] ) ) RETURN SUMX ( t, [Total_] )BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi ripstaur ,
Create a measure like so:
Measure = VAR t = SUMMARIZE ( 'Table', 'Table'[Region], 'Table'[County], "Total_", MAX ( 'Table'[County total cases] ) ) RETURN SUMX ( t, [Total_] )BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks so much. Both of these worked, but the DAX code was the way to go. I'm sorry I was unable to accept it right after you posted it, but for some reason I could not log into the Forum for a couple of weeks.
Hi ripstaur, Can you try pasting the excel sample data table in as a reply. That should work
Thanks,
Namish B
1 C Wonderful 364 1308 1 2305 1 B Wonderful 364 1587 2 1856 1 A Wonderful 364 1 D Eagle 160 1 F Mockingbird 96 1 E Robinette 83 1 G Cowpie 60 1 H Georgeana 90 1 I Draggit 149 1 J Lackett 152 1 K Pickit 154 2 L Simpson 269 2 M Simpson 269 2 N Ronson 190 2 O Johnson 129 2 P Albemarle 120 2 Q Johannesburg 177 2 R Kimtown 168 2 S Popular 132 2 T Wilherm 190 2 U Sampson 101 2 V Fuchness 111 Namish,
What I want to be able to do is calculate a total by region. You can see that the values for facilities C, B and A are all the same (Because they each take on the value for “Wonderful County.” I need to be able to get the total by region without the duplicates.
What I’m looking for is the two totals in cells E2 and EE3; what I am currently getting is the totals in G2 and G3.
Thanks for your help!
Best regards,
Rip
ripstaur , Not very clear. But isinscope should help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
example
if(isinscope ([Table[country]), calculate([measure], filter(Table,Table[region]="Region")), [measure], )
Unless I am misunderstanding, what you may be able to do is this:
Duplicate the table, and in the new table, select the column that has your duplicate entries, then in "Remove rows" select "Remove duplicates".
Then create a one-to-many relationship with your original and duplicate table.
Then do your query where you got it to sum up the values for each county for their individual totals. May need to incorporate the RELATEDTABLE function into it.
This way you can retain your original data while also having a table that has the cumulative totals for the different counties.
Delete/change the columns for the separate tables as needed.
