Filtering data captured from a toggle button.

[TJCappy]

I have a list of items called "Items" with the following columns:

ID,

Department,

Product number,

Product Name,

Purchase Price,

Markup %

Sale Price

New Item - This is a toggle button, that displays yes or no on the canvas app form but records the response in the list as true or false.

I have the following formula to give me a total number of items in the list:

    --     Item Total =

                    COUNT('Items'[ID])

I then have a formula gives me the number of items per department:

    --     Item total for Dept U16 =

                     CALCULATE(

                                [Item Total],

                                Filter('Items', Items[Department] = "U16"

)

now I want to filter the number of items for Dept U16  to show how many are new.

I have tried 

        --Total New Items for Department U16 =

           CALCULATE(

                      [Item total for Dept U16 ],

                      FILTER( 'Items', 'Items'[Department] = "U16" && 'Items'[New Item] = "yes" )

)

but I get a "Blank" response

but I know I have 420 new Items for Dept U16

I tried changing the "yes" in the formula  to true  but then get this message

"Calculation error in 'Measures table 01'[Total New Items for Department U16]:

Dax comparison operations do not support comparing values of the type Text with values of type True/False.

Consider using the VALUE or FORMAT function to convert one of the values."

Can anyone please assist me in resolving this conundrum.

Thank you

[amitchandak]

@TJCappy , First of all check that 'Items'[New Item]  is a column. Also, check what are distinct values. You can put in table visual and check

yes <> Yes in text comparison

if it is a measure share calculation

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[TJCappy]

Hi Amit,

Thanks for your comments

As you can see below 'Items'[NewItem]   is a column.

I am unsure what you mean when you say

"Also, check what are distinct values. You can put in table visual and check yes <> Yes in text comparison"

The calculations are

Measure 1

Item Total =

                    COUNT('Items'[ID])

Measure 2

Item total for Dept U16 =

                     CALCULATE(

                                [Item Total],

                                Filter('Items', Items[Department] = "U16"

)

Measure 3

Total New Items for Department U16 =

           CALCULATE(

                      [Item total for Dept U16 ],

                      FILTER( 'Items', 'Items'[Department] = "U16" && 'Items'[New Item] = "yes" )

)

The following table is the sample data taken directly from the table,  (I ommitted the product name for the sake of privacy)

ID	Department	ProductNumber	Product Name	PurchasePrice	Markup%	SalePrice	NewItem
1	U710	DAM-0Z-JWJ0-0Z-2	Purposely omitted	$3,005.00	133%	$7,000.00	FALSE
2	U001	DB2-0Z-YJD0-0Z-1	Purposely omitted	$3,300.00	52%	$5,000.00	FALSE
3	U001	DB1-0Z-APM0-0Z-1	Purposely omitted	$1,000.00	50%	$1,500.00	FALSE
4	U001	DB3-0Z-9CN0-0Z-1	Purposely omitted	$700.00	43%	$1,000.00	FALSE
5	U710	DB2-0Z-JAR0-0Z-1	Purposely omitted	$450.00	122%	$1,000.00	TRUE
6	U710	DB2-0Z-4LV0-0Z-3	Purposely omitted	$300.00	233%	$1,000.00	FALSE
7	U710	DAM-0Z-6T31-0Z-7	Purposely omitted	$900.00	250%	$3,150.00	FALSE
8	U16	DB2-0Z-XXH0-0Z-1	Purposely omitted	$7,000.00	257%	$25,000.00	FALSE
9	U710	DB3-0Z-93U0-0Z-3	Purposely omitted	$1,008.00	98%	$2,000.00	TRUE
10	U16	DAK-0Z-M6J0-0Z-2	Purposely omitted	$10,500.00	90%	$20,000.00	TRUE
11	U16	DAJ-0Z-QQ60-0Z-2	Purposely omitted	$500.00	3900%	$20,000.00	TRUE
12	U710	DB3-0Z-ARR0-0Z-2	Purposely omitted	$300.00	1567%	$5,000.00	FALSE
13	U710	DA8-0Z-DMK0-0Z-2	Purposely omitted	$300.00	567%	$2,000.00	FALSE
14	U710	DA8-0Z-DMH0-0Z-2	Purposely omitted	$300.00	1567%	$5,000.00	FALSE
15	U710	DAL-0Z-Z3B0-0Z-3	Purposely omitted	$300.00	233%	$1,000.00	FALSE
16	U710	DAL-0Z-2VB0-0Z-2	Purposely omitted	$300.00	567%	$2,000.00	TRUE