Measures for Blank or Not Blank

[jimmyg706]

Hi All

I want to create some quality check measures for a dataset and Im really struggling. Its to flag if there is data missing for must have fields/columns that need to be  filled in. I don’t want calculated Columns as I  don’t need any slicers for this this.

I have a list of applications  with 5x key Columns of Data – In yellow See Fig 1 below. 

There are say 5x Columns (Columns A-F)  that are critical pieces of data which simply must have data i.e must not be empty

So its either empty or not empty, but they should have text data in there.

So I was going to create 5x separate measures for Columns 1-5 below. Then add all the measures together to get a % complete.

So if its blank I output a ZERO and if its not empty I output a true value of 1.

The idea then is that I then have a % complete for the 5x columns.

However Im really struggliing to approach this in the right way and need some assistance.

Thanks In

Fig 1

Datset Below

APP	Column 1	Column 2	Column 3	Column 4	Column 5
App 1	DATA
App 2	DATA	DATA
App 3	DATA	DATA	DATA
App 4	DATA	DATA	DATA	DATA
App 5	DATA	DATA	DATA	DATA	DATA

Visuliation of Measures

APP	Column 1	Column 2	Column 3	Column 4	Column 5	Measure Column 1	Measure Column 2	Measure Column 3	Measure Column 4	Measure Column 5	Add Measures Together	% Complete Measure
App 1	DATA					1	0	0	0	0	1	20%
App 2	DATA	DATA				1	1	0	0	0	2	40%
App 3	DATA	DATA	DATA			1	1	1	0	0	3	60%
App 4	DATA	DATA	DATA	DATA		1	1	1	1		4	80%
App 5	DATA	DATA	DATA	DATA	DATA	1	1	1	1	1	5	100%

[v-junyant-msft · Accepted solution]

Hi @jimmyg706 ,

You measures are right. But if you have a very large number of columns, you will need to create a very large number of measures to fulfill your needs, and this can become cumbersome. There's no way this can be done with just one measure either.
If you can use Power Query Editor, it may be easier but it will add six more columns in your table without any measures. This method is not much different in terms of memory consumption than if you were to create a lot of measures, but it can be much easier to work with.
First, in Power Query and choose all of these columns and replace values:

Still choose these columns and click Unpivot:

Choose column "Attribute" and column "Value", then select Pivot:

And the final output is as below:

Then add a custom column and change the data type into Whole Number:

Choose "Close & Apply":

Use this DAX to create a measure:

Percentage = 
VAR _a = MAX('Table'[Custom])
VAR _b = COUNTROWS(ALL('Table'))
RETURN
DIVIDE(_a, _b)

And the final output is as below:

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

[jimmyg706]

Im sure there is an easier way, but I achived my desired affect

[jimmyg706]

Ive managed to get the measures

QA Colmumn 1 Measure = if(max('Sheet1'[Column 1])="",0,1)
QA Colmumn 2 Measure = if(max('Sheet1'[Column 2])="",0,1)
QA Colmumn 3 Measure = if(max('Sheet1'[Column 3])="",0,1)
QA Colmumn 4 Measure = if(max('Sheet1'[Column 4])="",0,1)
QA Colmumn 5 Measure = if(max('Sheet1'[Column 5])="",0,1)

Now I just need to add them all and get a %

[jimmyg706]

So for a clauclated column this would work

QA Colmumn 1 = if(ISBLANK(Sheet1[Column 1]),0,1)
QA Colmumn 2 = if(ISBLANK(Sheet1[Column 2]),0,1)
QA Colmumn 3 = if(ISBLANK(Sheet1[Column 3]),0,1)
QA Colmumn 4 = if(ISBLANK(Sheet1[Column 4]),0,1)
QA Colmumn 5 = if(ISBLANK(Sheet1[Column 5]),0,1)

But thats adding data.

Still checking a measure to produce the same result