Stacked bar graph question

[TBStacey]

I'm trying to create a bar graph that shows the "Earliest Confirmed Date" of a test on the X axis and the number of properties (PIN) confirmed via that test on the Y axis.

Since more than one test is done for a given property (PIN), a given property may have more than one confirmed date. In this bar graph, I only want to show the very first (earliest) confirmed date. I created a measure to do this as below:

The problem is, I can't get Power BI to add this measure into the X axis. I keep trying to move the measure (highlighted on right) to the X axis (highlighted on left). No luck.

Any idea what I am doing wrong? Help!

[lbendlin]

In column charts the X axis must be fed by a column.  If you want it to be fed by a measure then you need to materialize that measure through pre-existing buckets (ie a disconnected table with all expected values.

[TBStacey]

I'm unsure how to do that, so I tried using Copilot to get language to use in Power query. But, I only get errors. This is the power query langauge I used:

= Table.AddColumn(#"Reordered Columns2", "Earliest Date Confirmed by PIN", each Table.Group(
    my query name here, {[animal_state]}, {{"Earliest Date Confirmed by PIN", each List.Min([Confirmed Date]), type date}}))

Any ideas on where I am going wrong?

[lbendlin]

This is something you need to do in DAX, not in Power Query.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[TBStacey]

Let's say the data looks like this. I want to know the earliest confirmed date for a given animal_state. I also want to know the earliest confirmed date for a given PIN, but I'll start with animal_state because I think that may be simpler.

PIN	animal_state	Confirmed Date
ABC	MN	1/1/2024
ABC	MN	1/2/2024
AAA	MN	1/3/2024
AAA	MN	1/3/2024
AAA	MN	1/5/2024
AAA	MN	1/6/2024
AAD	MN	1/7/2024
AAD	MN	1/5/2024
AAD	MN	1/5/2024
BAD	MI	1/6/2024
BAD	MI	1/7/2024
BAD	MI	1/8/2024
BAD	MI	1/15/2024
BAD	MI	1/15/2024
BCD	MI	1/14/2024
BCD	MI	1/8/2024
CBA	KS	2/4/2024
CBA	KS	2/16/2024
CBA	KS	2/16/2024
CBA	KS	2/16/2024
CBA	KS	2/16/2024
CBA	KS	2/17/2024
CBA	KS	2/18/2024
CBA	KS	2/19/2024
CBA	KS	2/19/2024
CCD	KS	2/20/2024
CCD	KS	2/21/2024
CCD	KS	2/7/2024
CED	KS	2/8/2024
CED	KS	2/9/2024
CED	KS	2/8/2024
CED	KS	2/8/2024
CED	KS	2/8/2024
CED	KS	2/8/2024
CRD	KS	2/9/2024
CRD	KS	2/10/2024
CRD	KS	2/10/2024
CRD	KS	2/10/2024
CRD	KS	2/10/2024
DED	CA	3/2/2024
DED	CA	3/2/2024
DED	CA	3/2/2024
DED	CA	4/2/2024
DED	CA	2/29/2024
DAD	CA	2/29/2024
DAD	CA	2/29/2024
DAD	CA	2/29/2024
DAD	CA	3/1/2024
DEC	CA	3/2/2024
DTF	CA	3/2/2024
DTF	CA	3/2/2024
DTF	CA	3/2/2024
DTF	CA	3/2/2024
EED	TX	2/4/2024
EED	TX	2/4/2024
EED	TX	2/4/2024
EED	TX	4/3/2024
EED	TX	4/4/2024
EED	TX	4/5/2024
EED	TX	4/6/2024
EAD	TX	4/7/2024
EAD	TX	4/7/2024
EAT	TX	4/7/2024
EAT	TX	4/7/2024
EAT	TX	4/2/2024
EAT	TX	3/15/2024
EAT	TX	3/15/2024
ERD	TX	4/7/2024
ERD	TX	3/15/2024

I understand I need the data to be pivoted somehow like this (which I did manually) so I can see the earliest confirmed date per animal_state:

PIN	animal_state	Confirmed Date
ABC	MN	1/1/2024
AAA	MN	1/3/2024
AAD	MN	1/5/2024
BAD	MI	1/6/2024
BCD	MI	1/8/2024
CBA	KS	2/4/2024
EED	TX	2/4/2024
CCD	KS	2/7/2024
CED	KS	2/8/2024
CRD	KS	2/9/2024
DAD	CA	2/29/2024
DED	CA	3/2/2024
DEC	CA	3/2/2024
DTF	CA	3/2/2024
EAT	TX	3/15/2024
ERD	TX	3/15/2024
EAD	TX	4/7/2024

What I'd like as a final stacked bar graph is something like this:

Thanks.

[lbendlin · Accepted solution]

The first part is simple - you can use the implicit measures

For the second part ( the chart ) you need a couple of things - for a proper implementation  you need a calendar table for the X axis, and then a measure for the mapping.  Or you can cheat and create a calculated table that materializes the prior result. This can be done by "borrowing"  the DAX code from the visual

and cleaning it up as

Table2 = SUMMARIZECOLUMNS(
			'Table'[PIN],
			'Table'[animal_state],
			"MinConfirmed_Date", CALCULATE(MIN('Table'[Confirmed Date]))
		)

Then you can render your column chart.

or in categorical axis

see attached