COUNTIF type formula in Powerpivot which ignores blanks

[Highlander Mc]

Hi

I am trying to create a COUNTIF type formula in Powerpivot and would appreciate any help on where I'm going wrong.

My data set looks something like this and I am trying to do a distinct count on the REF column based on ID and not counting blanks. I would then like to be able to have the count change if I filter my data to only show certain months

Month	ID	REF	1st Answer I'm Looking For	2nd Answer if filtering for only Feb and March
Jan	1	A	4
Jan	2	B	3
Feb	1	C	4	3
Feb	2	D	3	2
Feb	1		4	3
Mar	2		3	2
Mar	1	E	4	3
Mar	1	F	4	3
Mar	2	G	3	3

<tbody>
</tbody>

So far I managed the following formula which seems to get me half way there.(1st Answer)
=calculate(countax(distinct('TABLE'[Ref]),'TABLE'[Ref]),ALLEXCEPT('TABLE','TABLE'[id]))

However the moment I try to add another filter or calc to take the Month column into account (for 2nd Answer) I end up with errors or a count of all records.

Any help will be appreciated. I am a beginner at Dax/powerpivot so feel free to rip the formula apart and suggest a better way to approach it.

Thanks

 

[Matt Allington]

DAX is seemingly simple, yet it takes time to learn. Are you trying to write calculate columns here? That's what it looks like, yet that is not really how DAX is designed to work. Read this article I wrote to help Excel users understand the difference. Calculated Columns vs Measures in DAX - Excelerator BI

you should be thinking about writing measures, and then putting those measures in a pivot table to get the answer you want. The measure you need (I think) is =countrows(distinct(Table[ref]))