Count all non-zero or non-null values at a specific row

[jduma]

Hi all,

I am very new to Power BI, but I am loving it already. I am using it to quickly represent and sift through a SQL database. We do a lot of sample testing and I want a way of indicating how many samples are currently remaining since last inspection. I am not sure what would be the best way to do this. For context a 0 is a failed/terminated sample and a null represents a missing sample. 

Appreciate any help.

Sample	Months	Sample Rating (0-10)
1	16	10
1	32	4
1	44	0

[v-chenwuz-msft]

Hi @jduma ,

Have your problem been solved? if yes, please mark it as solution so that some one has the same preblem as you can find the answer quickly.

Maybe you can try this expression, but befor this you need create a Dimension table for your sample, in order to add one row named "blank/0".

Measure_count =
VAR _1 =
    COUNTROWS ( FILTER ( 'Table', [Sample Rating (0-10)] > 0 ) )
VAR _2 =
    COUNTROWS ( FILTER ( ALL ( 'Table' ), [Sample Rating (0-10)] <= 0 ) )
RETURN
    IF ( SELECTEDVALUE ( 'Dimension'[Sample] ) = "Blank/0", _2, _1 )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[Samarth_18]

Hi @jduma ,

You can use measure for it:-

measure = countrows(filter('table','table[Sample Rating (0-10)] = 0 || 'table[Sample Rating (0-10)] = blank()))

Thanks,

Samarth

[jduma]

How would I do it for the current highest months number? IF statement?

[Samarth_18]

@jduma Could you please elaborate it with example?

[jduma]

So with the below example at the most recent inspection only sample 2 remains. Sample 1 has failed at 40 months and sample 3 is missing. This is so we can summarize how many samples are still left in the field. A sample of 0 or null(blank) has been removed and no longer requires inspection. 

Sample	Months	Sample Rating (0-10)
1	16	10
1	40	0
2	16	10
2	40	9
3	16	10
3	40	null

[Samarth_18]

Try this:-

failed_sample =
calculate (count('table'[Sample]),
    FILTER (
        'table',
        'table'[Sample Rating (0-10)] = 0
    )
)

missing_sample =
calculate (count('table'[Sample]),
    FILTER (
        'table',
        'table'[Sample Rating (0-10)] = BLANK()
    )
)