Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I have 2 columns of data; Product & Minimum order quantity. However sometimes I can have mission data in column #3 and I want to be able to count those value of a product that are either with data, or blank, if the same product has both a zero & number value. The goal is to count up the number of items that are not uniformly containing the data they should [Apple & Orange], without being skewed by products that are completely blank [Strawberry].
Apple | 50 |
Apple | 0 |
Apple | 0 |
Orange | 0 |
Orange | 20 |
Strawberry | 0 |
Strawberry | 0 |
Solved! Go to Solution.
@Anonymous , Create a new colum
column =
var _cnt_0 = countx(filter(table,[Product] =earlier([Product]) && [Qty]=0),[Product])
var _cnt = countx(filter(table,[Product] =earlier([Product]),[Product])
return
if(_cnt_0 = 0 || _cnt =0,"Uniform","Non Uniform")
Hi @Anonymous ,
You may create measure like DAX below.
Measure1 =
VAR _CountAll =
COUNTROWS ( FILTER ( Table1, Table1[Product] = MAX ( Table1[Product] ) ) )
VAR _CountZero =
COUNTROWS (
FILTER (
Table1,
Table1[Product] = MAX ( Table1[Product] )
&& Table1[quantity] = 0
)
)
VAR _CountNoZero =
COUNTROWS (
FILTER (
Table1,
Table1[Product] = MAX ( Table1[Product] )
&& Table1[quantity] <> 0
)
)
RETURN
IF (
_CountZero = _CountAll
|| _CountNoZero = _CountAll,
"Uniformly",
"Not Uniformly"
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You may create measure like DAX below.
Measure1 =
VAR _CountAll =
COUNTROWS ( FILTER ( Table1, Table1[Product] = MAX ( Table1[Product] ) ) )
VAR _CountZero =
COUNTROWS (
FILTER (
Table1,
Table1[Product] = MAX ( Table1[Product] )
&& Table1[quantity] = 0
)
)
VAR _CountNoZero =
COUNTROWS (
FILTER (
Table1,
Table1[Product] = MAX ( Table1[Product] )
&& Table1[quantity] <> 0
)
)
RETURN
IF (
_CountZero = _CountAll
|| _CountNoZero = _CountAll,
"Uniformly",
"Not Uniformly"
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Create a new colum
column =
var _cnt_0 = countx(filter(table,[Product] =earlier([Product]) && [Qty]=0),[Product])
var _cnt = countx(filter(table,[Product] =earlier([Product]),[Product])
return
if(_cnt_0 = 0 || _cnt =0,"Uniform","Non Uniform")
@Anonymous
Can you show what is the expected output from the sample ou have given?
And, you can include more data with your sample for clear understanding.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hopeful output is to show "2 products have incomplete data [minimum value & zero value recorded], 1 product is a flat zero in minimum order values entered"
@Anonymous
Try this as a measure:
Find Missing =
var crow =COUNTROWS('Table')
var czero =
CALCULATE(
COUNTROWS('Table'),
'Table'[Value] = 0
)
return
IF(
crow = czero ||
(ISBLANK(czero)),
BLANK(),
"Missing"
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |