Hi everyone
I have 3 tables.
1) components table: each component is identified by an ID and has some information attached to it
2) Activities table: each activity is performed on a single component, so for a component there's more than one activity
3)location table: it represents the territorial structure of the company, for example city>state
relations between tables are:
1)activities to components based on component ID
2)Components to location using lowest level of location (i.e components are located in a city, the location table attaches state, region and so on)
now what i have to do is to filter activities based on some time conditions and activity type and output an "ok"/"not ok" result, for each component. the measure that i'm building goes like this:
Component state =
CALCULATE(
IF(
CALCULATE(COUNT('activitytable'[ID]),'activity_table'[activity_type]="selected type")>=1
,
"ok",
"not ok"),
CROSSFILTER(Components[ID],'activity_table'[ComponentID],Both),
CROSSFILTER(Components[City],locationTable[City],Both),
FILTER('activity_table',
...some filter conditions...))
now the problem is that filters work ok and give the correct result, but if rows are duplicated for each state, if I put this measure in a table where columns are State (from location table), component (from components table), and this measure.
How can I remove duplicated values? i tried many solutions but i don't seem to find the correct one
