Creating Clustered Column using measure

[Anonymous]

Hi Datanauts!

Seeking your expert advice. 

I've been stumped in trying to create a clustered column using a measure.

I have a dataset that gets populated and tracks the unique ID of a client, date, and stage. I have created a measure that returns the latest stage ("Latest_Option") based on a date slicer for each unique client. The measure works great at returning the most recent Stage. See below:

notice the unique ID, date, and latest option. "Latest Option" measure changes dynamically as the date slicer is moved and works as expected. 

 This is the measure i used to create "Latest_Option"

Latest_Option = 
var _MaxDate =
CALCULATE(MAX(Data[Date]), FILTER(ALLSELECTED('Data'), Data[Unique_ID] = MAX(Data[Unique_ID])))
return
CALCULATE(MAX(Data[Stage]), FILTER('Data', Data[Date] = _MaxDate))

What I'm having trouble creating is the clustered column (see the chart) but instead of using Stage as in the example I'd like to replace it with "Latest_Option" such that it will dynamically change to return the chart of the Latest_Option based on unqiue clients otherwise the data is double couting. 

How do I best achieve this? 

I've attached a .pbix sample here. 

Thank you in advance. 

[v-yingjl]

Hi @Anonymous ,

Based on your measure, it returns a text value so that it cannot put in the value field in a column chart. Also, a measure is dynamic so taht it cannot be set as x-axis. The previous measure can be put in a card visual to show its value.

The other point that stumped me is 'the data is double counting'. Which expected output is your wanted? Could you please share more details for further discussion?

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl ,

Thank you for the reply. 

But the visual I have showing the clustered column chart is from a text column:

the above chart is an example of what i want but instead of using the column "stage" I would liek to use the measure "latest_option".

Re: double counting

The example above counts all the values in the "Stage" column and include all the rows of data. Instead I want to count the options by unique ID filtered by the latest date for that unique ID.

I would like to know the most recent count of options requested by client (unique ID). The date slicer allows me to look back to see the most up to date request of options during that time.

Another way to think about it is that I would like to show chart that counts distinct values in the measure "Latest_option".

I hope my explanation is clear.

Thank you again.