Help needed with DAX

[Anonymous]

So i have a data table with different project submitting weekly status report. I want to count active projects (which is anything other than not-started, and completed) and display this measure on a card. 

Is there any way I can count unique projects that contains multiple value in different column?

 

[Anonymous]

@Anonymous 

It would be great if you could mark my answer as THE answer (so that this helps others) and I would also appreciate kudos (thumbs-up). Thanks.

[Anonymous]

Seems like you have not noticed the second measure I've given you...

[Anonymous]

My bad its all working great! Thank you 

[Anonymous]

let me give it a try

[Anonymous · Accepted solution]

[Active Project Count] =
COUNTROWS(
	FILTER(
		DISTINCT( T[Project] ),
		ISEMPTY(
			CALCULATETABLE(
				T,
				KEEPFILTERS(
					T[Status] in {
						"not started",
						"completed"
					}
				)
			)
		)
	)
)

[Anonymous]

When I try to do this DAX command again, I am getting a return with blank rows? Any help?

[Anonymous]

Without any info on/data about/knowledge of the environment where this happens? I'm very sorry but I can't give you any advice. I'm not a fortune-teller 🙂

[Anonymous]

For some reason I am not getting the correct answer using this code above.

[Anonymous]

That's not informative enough. What precisely is wrong and what you expect? This is the info you have to supply for me to be able to troubleshoot.

[Anonymous]

This is not working. I would like to know how many distinctcount of project that does not have a not started or completed status. When i did the function on the top, I am just getting distinctcount of the projects

[Anonymous]

OK. Let's get this straight. You want a measure that will look for projects that do not have "not started" and do not have "completed" as their status in the current context, right? Then you want to count them. By "current context" I mean all the filters that may have been put on the table. Which also means you want the measure to be totally responsive to any slicing and dicing. Right?

[Anonymous]

I would only want to use this value for a card. And yes, I would want to count distinct projects that does not have the status of not started and and completed. If the project status does not show not started, or completed it's assumed the project is current. Is there any way I can filter this information? Distinct count of projects that doesnt have variables x,y,z etc is something what I am looking for

[Anonymous]

[Active Project Count] =
CALCULATE(
	DISTINCTCOUNT( T[Project] ),
	KEEPFILTERS(
		not T[Status] in {
			"not started",
			"completed"
		}
	)
)