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 status says completed), which means I need to get the higest completed stage for that project. So i have added an order column also to find the max and determine where the project is.

 

Table: Project 2

 

Table: table

 

 

 

I have created a measure:

Measure = VAR A = MAX('Projects (2)'[Order])

RETURN
CALCULATE(VALUES('Table'[Stage]), 'Table'[Order] = A)
 
It worked well for a table chart:

But I want to show the same in Column graph where the X axis has the measure and the Y axis shows number of projects in that stage. I am not able to use the measure in the Y axis. Can you help? I have attached the PBIX file here for your referance. 

 

EXPECTED OUT COME: 

 

 

 
 

 

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:

     

     

     

8 Replies

  • 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

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    1 year ago

    Hi SDVN,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    1 year ago

    Hi SDVN,

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.


    Thank you.