Dynamically Update DAX with Slicer

[ZW]

Hi all,

I find this forum a valuable resource and am hoping someone can assist me with the below problem.

I have a sample set of personal records, with some data and some n/a.

Name	Birthday	Nationality	Gender	Age	Occupation	Address
Steve	22/04/1990	British	Male	31	Builder	Scotland
Alice	n/a	Irish	Female	n/a	n/a	Ireland
Bob	n/a	British	n/a	52	Teacher	n/a

My goal is to show how complete these records are (as in, not showing n/a) in the form of a percentage. I have managed to achieve this with 2 columns. The first one counts the number of n/a and returns a percentage:

Count n/a Percentage =
DIVIDE (
COUNTX ( FILTER ( 'Table', 'Table'[Birthday] = "n/a" ), [Birthday] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Nationality] = "n/a" ), [Nationality] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Gender] = "n/a" ), [Gender] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Age] = "n/a" ), [Age] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Occupation] = "n/a" ), [Occupation] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Address] = "n/a" ), [Address] ),
COUNTROWS ( 'Table' ) * 7
)

The second column calculates the reverse of this to show percentage complete

Percentage Complete = DIVIDE(100 - ('Table'[Count n/a Percentage]*100),100)

This works fine as seen below:

However, if I use Name as a slicer this percentage doesn’t change to show only the percentage complete in that row.

My ideal output would be this:

Any help would be greatly appreciated.

Thank you

[ZW]

Thank you, I didn't realise columns and measures behaved differently when it came to splicing. 

The new measure works perfectly!

[amitchandak · Accepted solution]

@ZW , I think this correct as measure.

Count n/a Percentage =
DIVIDE (
COUNTX ( FILTER ( 'Table', 'Table'[Birthday] = "n/a" ), [Birthday] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Nationality] = "n/a" ), [Nationality] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Gender] = "n/a" ), [Gender] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Age] = "n/a" ), [Age] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Occupation] = "n/a" ), [Occupation] )
+ COUNTX ( FILTER ( 'Table', 'Table'[Address] = "n/a" ), [Address] ),
COUNTROWS ( 'Table' ) * 7
)

Not as column , as first row do not have any N/a and it 71 %

you can have column like

Count n/a Percentage =
DIVIDE (
if('Table'[Birthday] = "n/a" ,1,0 )
+if('Table'[Nationality] = "n/a" ,1,0 )
+if('Table'[Gender] = "n/a" ,1,0 )
+if('Table'[Age] = "n/a" ,1,0 )
+if('Table'[Occupation] = "n/a" ,1,0 )
+if('Table'[Address] = "n/a" ,1,0 ) ,
7
)

But my advice is use first one as a measure