Forum Discussion
Filtering/counts/if for 0 / Null / and negative value
Really need a helping hand to get an answer in my questions:
This is the table, I used a slicer to search their names (dashboard) but what I would like to see are:
When I try to search Anna I would like to see the Negative and Zero value under her name. The Anna values below are good but I what I really want to see all the errors under her name. Could that be possible?
Also, what is the formula if would like to count negative value and zero value and above 100 figures?
I tried if statements, counts but I'm getting errors. If DAX is simple as the fx of the excel it could be easier.
| Name | Figure 1 | Figure 2 | Figure 3 |
| Anna | -15 | 1200 | 0 |
| Fred | 4500 | 57 | 0 |
| Tim | 465 | 98 | 4 |
| Corel | 100 | 333 | 87 |
| Budd | -17 | 100 | 1 |
| Anna | 100 | 5 | 100 |
Hope would hear from you soon.
Hi cjb ,
To create a measure as below.
Measure = VAR f1 = CALCULATE ( COUNT ( Table1[Figure 1] ), FILTER ( Table1, 'Table1'[Figure 1] <= 0 || 'Table1'[Figure 1] > 100 ) ) VAR f2 = CALCULATE ( COUNT ( Table1[Figure 2] ), FILTER ( Table1, 'Table1'[Figure 2] <= 0 || 'Table1'[Figure 2] > 100 ) ) VAR f3 = CALCULATE ( COUNT ( Table1[Figure 3] ), FILTER ( Table1, 'Table1'[Figure 3] <= 0 || 'Table1'[Figure 3] > 100 ) ) RETURN f1 + f2 + f3
3 Replies
my data is not in a pivot.
I am thinking about some conditions in DAX or any code to get the specific integer
Hi cjb ,
To create a measure as below.
Measure = VAR f1 = CALCULATE ( COUNT ( Table1[Figure 1] ), FILTER ( Table1, 'Table1'[Figure 1] <= 0 || 'Table1'[Figure 1] > 100 ) ) VAR f2 = CALCULATE ( COUNT ( Table1[Figure 2] ), FILTER ( Table1, 'Table1'[Figure 2] <= 0 || 'Table1'[Figure 2] > 100 ) ) VAR f3 = CALCULATE ( COUNT ( Table1[Figure 3] ), FILTER ( Table1, 'Table1'[Figure 3] <= 0 || 'Table1'[Figure 3] > 100 ) ) RETURN f1 + f2 + f3
