Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
@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.
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 |
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |