Forum Discussion
Need help calculating Null and No null values in column
Hi parry2k
Test with this data
First Create a calculated column
year/weeknum = YEAR(Table1[Date])&"-"&WEEKNUM(Table1[Date],2)
Then Create two measures
count not null =
CALCULATE(COUNT(Table1[Date]),FILTER(ALLSELECTED(Table1),Table1[year/weeknum]=MAX(Table1[year/weeknum])&&Table1[Test Number]<>BLANK())) count null =
CALCULATE(COUNT(Table1[Date]),FILTER(ALLSELECTED(Table1),Table1[year/weeknum]=MAX(Table1[year/weeknum])&&Table1[Test Number]=BLANK()))Add them to a line chart
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply parry2k . All the cells on the new column are "Not Null" even if there's a value in the main column.
Super UserAnonymous not sure what you mean, if you can share sample data in excel and expected result, it will help. Read this post to get your answer quickly
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
parry2k , Thank you for the information provided. So, let me explain what I have. I have a table with thousands of records. One of the columns is “Test Number,” which has the test information when a user provides it. If they don’t have a test, a “blank” cell is generated. I want to be able to generate a chart to calculate the number of tests created over the empty cells. In this example (see below) it would be 20 tests over 11 empty cells (no tests provided). Thank you!
Date Test Number 11/12/2018 Test_03 11/14/2018 Test_15 11/16/2018 Test_16 11/18/2018 11/20/2018 Test_04 11/22/2018 11/24/2018 Test_01 11/26/2018 Test_02 11/28/2018 11/30/2018 Test_08 12/2/2018 12/4/2018 Test_09 12/6/2018 Test_10 12/8/2018 Test_11 12/10/2018 12/12/2018 12/14/2018 12/16/2018 Test_14 12/18/2018 12/20/2018 Test_12 12/22/2018 Test_13 12/24/2018 12/26/2018 Test_17 12/28/2018 Test_18 12/30/2018 Test_19 1/1/2019 Test_20 1/3/2019 1/5/2019 1/7/2019 Test_05 1/9/2019 Test_06 1/11/2019 Test_07 - parry2k
Anonymous so you want to show value in card visual 11 divided by 20 from this example data? correcT?
parry2k Maybe my example needs more elaboration than I thought. There is another column called “Week.” So, I want to be able to create a chart like the one below which will show the blue line to be the number of cells with data (Test_#) by week and yellow line that indicates the number of empty cells by week. Test to No Test Ratio for week 1 would be 6/2
Week
Test Number
week1
Test_03
week1
Test_15
week1
Test_16
week1
week1
Test_04
week1
week1
Test_01
week1
Test_02
week2
week2
Test_08
week2
week2
Test_09
week2
Test_10
week2
Test_11
week2
week2
week3
week3
Test_14
week3
week3
Test_12
week3
Test_13
week3
week4
Test_17
week4
Test_18
week4
Test_19
week4
Test_20
week4
week4
week4
Test_05
week4
Test_06
week4
Test_07
Test to No Test Ratio
Test to No Test Ratio