Make a measure a calculated column in order to make it a slicer

[MichaelaMul]

Hi,

I am trying to change my measure into a calculated column so that I can make it a slicer, to filter if the product is "NEW". There's multiple Geographies and Times in the Data, so I would want it to be New within that specific geography and time.

The measure is below:

New $ = IF(values('Data'[Dollar Sales])=values('Data'[Dollar Sales Change vs YA]),"NEW","--")

Calculated column I made, but it doesn't work

NEW $$ = 
IF(
HASONEVALUE
('Data'[Product]) && Data[New $]= "NEW","NEW","--")

I also tried the following calculated column and this also didn't work

NEW $$ = 
var DollarSales = 
CALCULATE(
        SUM([Dollar Sales]),
        FILTER('Data','Data'[Geography] = EARLIER('Data'[Geography]) && 'Data'[Time] = EARLIER('Data'[Time]))
    )
var DollarSalesChg = 
CALCULATE(
        SUM([Dollar Sales Change vs YA]),
        FILTER('Data','Data'[Geography] = EARLIER('Data'[Geography]) && 'Data'[Time] = EARLIER('Data'[Time]))
    )
var _result =
IF( HASONEVALUE('Data'[Product]) &&
    DollarSales = DollarSalesChg,
    "NEW",
    "NOT NEW"
)
RETURN
_result

Thanks!

[Anonymous]

Hi @MichaelaMul ,

According to your code, I have some questions on your measure and calculated column.

1. You use VALUES() in your measure, I think it will return error due to VALUES() will return a table, table = table is incorrect.

2. I think the logic of [New $$] (the second one) should be correct. So I think this should be caused by your data model.

Here I suggest you to show more details or share a sample file with us and show us a screenshot with the result you want. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MichaelaMul]

Hi! Thank you for your reply. The measure I'm not using in the table, so I don't think its related.

Below is a table with the measure and the calculated column together. As you can see the measure is working but the calculated column is not.

here's some sample data:

Geography	Time	Product	Brand	Custom Hood Segment	Volume Sales	Volume Sales Change vs YA	Unit Sales	Unit Sales Change vs YA	Dollar Sales	Dollar Sales Change vs YA	ACV Weighted Distribution	ACV Weighted Distribution Year Ago	Volume per $MM per Item	Volume per $MM per Item Change vs YA	Units per $MM per Item	Units per $MM per Item Change vs YA	Dollars per $MM per Item	Dollars per $MM per Item Year Ago	Price per Unit	Price per Unit Year Ago	Weighted Average Base Price Per Unit Year Ago	Weighted Average Base Price Per Unit	Segment	Custom Hood Size Value	Volume Share of Segment	Custom Hood Category Value	Custom Hood Sub Category Value	Custom Hood Type Value	Volume Sales % Change vs YA	Unit Sales % Change vs YA	Dollar Sales % Change vs YA	Volume Sales Year Ago	Unit Sales Year Ago	Dollar Sales Year Ago
Store A	Latest 13 Weeks	Strawberry Milk	Flavored Farm	MILK	1,658	10	6,630	41	$40,320	$1,326	24.49287	21.52241	0.109935	-0.01319	0.439741	-0.05277	2.674218	2.914695	$6.08	5.918036	6.322956	6.400841	Oat VAM	32 OZ 1 CT	0.174746	VALUE - ADDED MILK	SPECIALTY MILK	N/A	0.62%	0.62%	3.40%	1647.25	6589	38993.94
Store A	Latest 4 Weeks	Strawberry Milk	Flavored Farm	MILK	488	36	1,951	145	$12,262	$714	21.06136	18.94473	0.036071	-0.00108	0.144284	-0.00431	0.906836	0.950126	$6.29	6.394142	6.453155	6.413196	Oat VAM	32 OZ 1 CT	0.16745	VALUE - ADDED MILK	SPECIALTY MILK	N/A	8.03%	8.03%	6.19%	451.5	1806	11547.82
Store B	Latest 13 Weeks	Strawberry Milk	Flavored Farm	MILK	24,244	-2,874	96,975	-11,497	$590,450	($65,396)	17.95659	17.36005	0.158188	-0.03667	0.632751	-0.14667	3.852614	4.712556	$6.09	6.046232	6.249021	6.301643	Oat VAM	32 OZ 1 CT	0.227253	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-10.60%	-10.60%	-9.97%	27117.99	108471.9	655846.6
Store B	Latest 4 Weeks	Strawberry Milk	Flavored Farm	MILK	7,753	-863	31,012	-3,451	$183,381	($18,197)	16.68932	16.23232	0.053404	-0.01085	0.213616	-0.0434	1.263174	1.503352	$5.91	5.849139	6.202723	6.284832	Oat VAM	32 OZ 1 CT	0.235299	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-10.01%	-10.01%	-9.03%	8615.709	34462.84	201577.9
Store C	Latest 13 Weeks	Strawberry Milk	Flavored Farm	MILK	24,382	-2,754	97,527	-11,018	$593,730	($62,529)	11.78241	11.39344	0.105555	-0.02277	0.422222	-0.09107	2.562254	3.102661	$6.09	6.045966	6.248618	6.299692	Oat VAM	32 OZ 1 CT	0.167258	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-10.15%	-10.15%	-9.53%	27136.24	108544.9	656259
Store C	Latest 4 Weeks	Strawberry Milk	Flavored Farm	MILK	7,804	-816	31,215	-3,265	$184,590	($17,084)	10.92034	10.65505	0.034942	-0.00733	0.139769	-0.02932	0.826449	0.988909	$5.91	5.849041	6.20245	6.282883	Oat VAM	32 OZ 1 CT	0.171896	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-9.47%	-9.47%	-8.47%	8619.959	34479.84	201674
Store A	Latest 13 Weeks	Coffee Milk	Cow	MILK	1,018	1,018	4,070	4,070	$11,658	$11,658	12.50493		0.132182	0.132182	0.52873	0.52873	1.51446		$2.86			2.987117	Oat VAM	32 OZ 1 CT	0.107272	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store A	Latest 4 Weeks	Coffee Milk	Cow	MILK	300	300	1,199	1,199	$3,326	$3,326	12.71846		0.036709	0.036709	0.146836	0.146836	0.407258		$2.77			2.985893	Oat VAM	32 OZ 1 CT	0.102908	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store B	Latest 13 Weeks	Coffee Milk	Cow	MILK	1,051	1,051	4,202	4,202	$12,040	$12,040	1.153139		0.106748	0.106748	0.426992	0.426992	1.223338		$2.87			2.988447	Oat VAM	32 OZ 1 CT	0.009848	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store B	Latest 4 Weeks	Coffee Milk	Cow	MILK	309	309	1,237	1,237	$3,430	$3,430	1.15691		0.030734	0.030734	0.122935	0.122935	0.340802		$2.77			2.98591	Oat VAM	32 OZ 1 CT	0.009387	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store C	Latest 13 Weeks	Coffee Milk	Cow	MILK	1,051	1,051	4,202	4,202	$12,040	$12,040	0.749555		0.069388	0.069388	0.27755	0.27755	0.795185		$2.87			2.988447	Oat VAM	32 OZ 1 CT	0.007207	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store C	Latest 4 Weeks	Coffee Milk	Cow	MILK	309	309	1,237	1,237	$3,430	$3,430	0.749286		0.019905	0.019905	0.07962	0.07962	0.220724		$2.77			2.98591	Oat VAM	32 OZ 1 CT	0.006813	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store B	Latest 13 Weeks	Chocolate Milk	Cow	MILK	3,862	3,862	8,379	8,379	$41,782	$41,782	5.836493		0.077523	0.077523	0.168199	0.168199	0.838758		$4.99			5.555639	Oat VAM	59 OZ 1 CT	0.036199	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store B	Latest 4 Weeks	Chocolate Milk	Cow	MILK	427	427	927	927	$5,153	$5,153	2.794423		0.017572	0.017572	0.038126	0.038126	0.212008		$5.56			5.641672	Oat VAM	59 OZ 1 CT	0.012964	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store C	Latest 13 Weeks	Chocolate Milk	Cow	MILK	4,546	4,546	9,863	9,863	$47,773	$47,773	3.894377		0.051392	0.051392	0.111503	0.111503	0.553968		$4.84			5.327271	Oat VAM	59 OZ 1 CT	0.031184	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store C	Latest 4 Weeks	Chocolate Milk	Cow	MILK	497	497	1,079	1,079	$5,763	$5,763	1.868921		0.011558	0.011558	0.025077	0.025077	0.138852		$5.34			5.413106	Oat VAM	59 OZ 1 CT	0.010952	VALUE - ADDED MILK	SPECIALTY MILK	N/A
Store B	Latest 13 Weeks	Vanilla Milk	Milk Farm	MILK	16	-27,996	44	-74,657	$239	########	0.019878	12.9631	0.09622	-0.17334	0.256587	-0.46223	1.40941	3.103848	$5.49	4.318003	4.984374	5.84483	Oat VAM	48 OZ 1 CT	0.000153	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-99.94%	-99.94%	-99.93%	28012.52	74700.06	322555.1
Store B	Latest 4 Weeks	Vanilla Milk	Milk Farm	MILK	4	-10,208	12	-27,221	$68	########	0.005393	12.47495	0.094023	-0.00508	0.250728	-0.01354	1.451674	1.063277	$5.79	4.023404	4.955478	5.789827	Oat VAM	48 OZ 1 CT	0.000134	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-99.96%	-99.96%	-99.94%	10212.32	27232.85	109568.8
Store C	Latest 13 Weeks	Vanilla Milk	Milk Farm	MILK	20	-37,887	55	-101,032	$261	########	0.044227	11.53449	0.069576	-0.15299	0.185535	-0.40796	0.942338	2.522529	$4.78	4.231597	4.724306	5.065225	Oat VAM	48 OZ 1 CT	0.00014	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-99.95%	-99.95%	-99.94%	37907.52	101086.7	427758.3
Store C	Latest 4 Weeks	Vanilla Milk	Milk Farm	MILK	8	-13,243	21	-35,315	$81	########	0.014319	11.02584	0.067899	-0.01178	0.181063	-0.03142	0.966241	0.853736	$3.92	4.015774	4.734723	3.920655	Oat VAM	48 OZ 1 CT	0.000172	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-99.94%	-99.94%	-99.94%	13250.82	35335.51	141899.4
Store A	Latest 13 Weeks	Original Milk	Cow	MILK	4,122	-1,857	8,243	-3,714	$32,299	($21,787)	24.67989	38.17365	0.271302	0.019337	0.542604	0.038673	2.126028	2.279338	$3.92	4.523118	4.947872	4.53571	Oat VAM	59 OZ 1 CT	0.434537	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-31.06%	-31.06%	-40.28%	5978.851	11957.7	54086.1
Store A	Latest 4 Weeks	Original Milk	Cow	MILK	1,725	262	3,450	524	$12,286	($2,380)	23.93593	34.06153	0.11225	0.0453	0.2245	0.0906	0.799503	0.671144	$3.56	5.012286	5.048668	4.369323	Oat VAM	59 OZ 1 CT	0.592212	VALUE - ADDED MILK	SPECIALTY MILK	N/A	17.91%	17.91%	-16.23%	1463	2926	14665.95
Store B	Latest 13 Weeks	Original Milk	Cow	MILK	144,277	-155,496	288,553	-310,992	########	########	37.44162	66.48398	0.451479	-0.11097	0.902958	-0.22193	3.989604	4.968045	$4.42	4.41647	4.931114	4.810683	Oat VAM	59 OZ 1 CT	1.352401	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-51.87%	-51.87%	-51.85%	299772.6	599545.2	2647874
Store B	Latest 4 Weeks	Original Milk	Cow	MILK	41,772	-44,467	83,544	-88,933	$369,360	########	35.71841	62.18433	0.134444	-0.03344	0.268889	-0.06689	1.188789	1.499465	$4.42	4.465666	4.869027	4.782189	Oat VAM	59 OZ 1 CT	1.267771	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-51.56%	-51.56%	-52.05%	86238.79	172477.6	770227.3
Store C	Latest 13 Weeks	Original Milk	Cow	MILK	190,814	-235,736	381,627	-471,471	########	########	27.39125	51.92336	0.311361	-0.21012	0.622722	-0.42023	2.761976	4.506101	$4.40	4.279342	4.680265	4.754536	Oat VAM	59 OZ 1 CT	1.308969	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-55.27%	-55.27%	-54.02%	426549.3	853098.6	3650701
Store C	Latest 4 Weeks	Original Milk	Cow	MILK	55,407	-69,666	110,814	-139,332	$486,222	########	26.20952	49.15613	0.093476	-0.06412	0.186951	-0.12823	0.831191	1.376578	$4.39	4.318965	4.630554	4.729149	Oat VAM	59 OZ 1 CT	1.220476	VALUE - ADDED MILK	SPECIALTY MILK	N/A	-55.70%	-55.70%	-54.99%	125072.9	250145.7	1080371

[Anonymous · Accepted solution]

Hi @MichaelaMul ,

Here I have a test by your sample and I think I find the solution.

According to your statement, I think your issue is that there are a measure and a calculated column in your report, when you select the slicer, the measure works but calculated column won't.

As far as I know, calculated column couldn't be dynamic like measure, it is calculated by the row filter and will show static result in your data model.
If you want to show dynamic results based on filters, a measure should be a better way.

For reference: Calculated Columns and Measures in DAX - SQLBI

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MichaelaMul]

What I would like to do is create a slicer for whether or not a product is new. I can't use a measure as a slicer so I was trying to use a calculated column. Is there another way to do this?

[dharmendars007]

Hello @MichaelaMul , 

You can try the below code...and also Earlier and Hasonevalue it may not work as expected in calculated column when dealing with multiple columns.

NEWStatus =
VAR CurrentGeography = 'Data'[Geography]
VAR CurrentTime = 'Data'[Time]
VAR DollarSales = CALCULATE(
SUM('Data'[Dollar Sales]),
'Data'[Geography] = CurrentGeography,
'Data'[Time] = CurrentTime)
VAR DollarSalesPrevYear = CALCULATE(
SUM('Data'[Dollar Sales]),
'Data'[Geography] = CurrentGeography,
SAMEPERIODLASTYEAR('Data'[Time])
VAR SalesChange = DollarSales - DollarSalesPrevYear
RETURN
IF(
HASONEVALUE('Data'[Product]) && SalesChange > 0,"NEW","NOT NEW")

If you find this helpful , please mark it as solution and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN