Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help needed with DAX

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's avatar
    Anonymous
    5 years ago
    [Active Project Count] =
COUNTROWS(
	FILTER(
		DISTINCT( T[Project] ),
		ISEMPTY(
			CALCULATETABLE(
				T,
				KEEPFILTERS(
					T[Status] in {
						"not started",
						"completed"
					}
				)
			)
		)
	)
)

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    [Active Project Count] =
COUNTROWS(
	FILTER(
		DISTINCT( T[Project] ),
		ISEMPTY(
			CALCULATETABLE(
				T,
				KEEPFILTERS(
					T[Status] in {
						"not started",
						"completed"
					}
				)
			)
		)
	)
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    Anonymous
    Not applicable
    5 years ago
    [Active Project Count] =
CALCULATE(
	DISTINCTCOUNT( T[Project] ),
	KEEPFILTERS(
		not T[Status] in {
			"not started",
			"completed"
		}
	)
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      let me give it a try

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      My bad its all working great! Thank you 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.