Forum Discussion

SDVN's avatar
SDVN
Helper I
1 year ago
Solved

Using measure for axis in a column graph

I want to show the current status or stage in which the project is in. If there is "stage 4" completed in the table that means the project is in stage 4, completing stage 1,2 and 3 (thought the statu...
Need Help
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    1 year ago

    Hi SDVN ,

     

    No problem just change the measure to get the order value instead of the Stage value something similar to this:

     

    Project by Status Withou Relationship = VAR _Stage = MAX('Table'[Order])
		VAR _temptable = CALCULATETABLE(
			SUMMARIZE(
				'Projects (2)',
                'Projects (2)'[Projects],
				"Status", MAX('Projects (2)'[Order])
			),
			CROSSFILTER(
				'Projects (2)'[Order],
				'Table'[Order],
				None
			)
		)
		RETURN
			COUNTROWS(FILTER(
				_temptable,
				[Status] = _Stage
			))

     

    Has you can see the result does not change:

     

     

     

MFelix's avatar
MFelix
Super User
1 year ago

Hi SDVN ,

 

Change your DAX code to the following:

 

Project by Status = 
VAR _Stage = MAX('Table (2)'[Stage]) 
VAR _temptable = SUMMARIZE('Table', 'Table'[Projects], "Status", MAX('Table'[Stages]))
Return
COUNTROWS(FILTER(_temptable, [Status] = _Stage ))

 

Final result:

 

Believe that your chart was not matching the data you gave.

 

 

SDVN's avatar
SDVN
Helper I
1 year ago

MFelix Thank you so much! What you have show is surely my solution but am not able to replicate in my report. Can i request you to help? 🙂

 

 

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi SDVN ,

     

    You problem is the relationship between the projects table and the stages you can remove the relationship or you can change your measure to be like this:

     

    Project by Status Withou Relationship = VAR _Stage = MAX('Table'[Stages])
		VAR _temptable = CALCULATETABLE(
			SUMMARIZE(
				'Projects (2)',
				'Projects (2)'[Projects],
				"Status", MAX('Projects (2)'[Stages])
			),
			CROSSFILTER(
				'Projects (2)'[Order],
				'Table'[Order],
				None
			)
		)
		RETURN
			COUNTROWS(FILTER(
				_temptable,
				[Status] = _Stage
			))

     

    Has you can see in the chart below Project By status is the one with the active relationship the other is this new one.

     

    But best option is to turn off the relationship and the initial measure will work properly

    • SDVN's avatar
      SDVN
      Helper I
      1 year ago

      Thank you so much! It worked but " MAX('Table'[Stages])" works as its a sample data and it has the stages as "Stage 1, stage 2, stage 3 etc but in the real senirio the stages look like, FPFV, PA, OLS, DBL etc. Which is why I added the order column using the "Table". 

       

      And for this the relationship was estabished: 

       

       

       

       I guess I can't depend on MAX('Table'[Stages] or 'Projects (2)'[Stages]. rather it has to depend on the max of "order" columns.

      • MFelix's avatar
        MFelix
        Super User
        1 year ago

        Hi SDVN ,

         

        No problem just change the measure to get the order value instead of the Stage value something similar to this:

         

        Project by Status Withou Relationship = VAR _Stage = MAX('Table'[Order])
		VAR _temptable = CALCULATETABLE(
			SUMMARIZE(
				'Projects (2)',
                'Projects (2)'[Projects],
				"Status", MAX('Projects (2)'[Order])
			),
			CROSSFILTER(
				'Projects (2)'[Order],
				'Table'[Order],
				None
			)
		)
		RETURN
			COUNTROWS(FILTER(
				_temptable,
				[Status] = _Stage
			))

         

        Has you can see the result does not change: