Measure Comparing All Unique Values in Table Column When Using IF Function - DAX

[diedrich08]

Hello, 

I am attempting to complete a measure and add as column to compare the "Concentration" to a guideline which is in another table.

I have the following table:

Date	Well	Parameter	Concentration
01/07/2016 0:00	MO_MW1	Benzene	0.003943
01/07/2016 0:00	MO_MW2	Benzene	0.005292
01/07/2016 0:00	MO_MW3	Benzene	0.0005
01/07/2017 0:00	MO_MW1	Benzene	0.027705
01/07/2017 0:00	MO_MW2	Benzene	0.030111
01/07/2017 0:00	MO_MW3	Benzene	0.0005
01/07/2018 0:00	MO_MW1	Benzene	0.005625
01/07/2018 0:00	MO_MW2	Benzene	0.018283
01/07/2018 0:00	MO_MW3	Benzene	0.0005
01/07/2016 0:00	MO_MW1	Chloride	10.42321
01/07/2016 0:00	MO_MW2	Chloride	22.95381
01/07/2016 0:00	MO_MW3	Chloride	37.10764
01/07/2017 0:00	MO_MW1	Chloride	46.84721
01/07/2017 0:00	MO_MW2	Chloride	75.34789
01/07/2017 0:00	MO_MW3	Chloride	48.45198
01/07/2018 0:00	MO_MW1	Chloride	10.41973
01/07/2018 0:00	MO_MW2	Chloride	24.55236
01/07/2018 0:00	MO_MW3	Chloride	18.30098

The script I am using is:

Exceed_Chem = 
var
GL = CALCULATE(MINX('Guidelines','Guidelines'[Value]),filter('Guidelines','Guidelines'[Parameter]=SELECTEDVALUE('Chemistry'[Parameter])))

Var
Conc = SELECTEDVALUE('Chemistry'[Concentration])

Return
if(Conc>GL,"TRUE","FALSE")

This is used so that both the exceedances and non-exceedances are flagged; however, when I use this fuction, I get a table that appears to loop through unique values in the selected column of the table. For example, I have one benzene measurement on 01/07/2016 at BV_MW1, but I am having 2 values provided in the summary table (0.0005 and 30.3). To clarify, the 30.3 is a chloride concentration from the original table that I filtered out of the original table to keep the post shorter.

Date	Well	Parameter	Concentration	Exceed_Chem
01/07/2016 0:00	BV_MW1	Benzene	0.0005	FALSE
01/07/2016 0:00	BV_MW1	Benzene	30.27886	FALSE

Can any provide any insights why this is happening and what i can do to resolve? 

Thanks!

Diedrich

[v-chuncz-msft · Accepted solution]

@diedrich08 ,

Take a look at Change how a numeric field is aggregated and try using SUM instead of SELECTEDVALUE.