Column with first possible date

[Sperling]

Hi,

I have a table that looks similar to this (lets just call the values [InputNumber])

Zone#	2022-12-01	2022-12-02	2022-12-03	2022-12-04	2022-12-05	2022-12-06	2022-12-07
Zone1			9			4
Zone2				5			8
Zone3						3
Zone4		7		7

Instead of this view, I want a table with two columns:

Zone#	First date with input
Zone1	2022-12-03
Zone2	2022-12-04
Zone3	2022-12-06
Zone4	2022-12-02

This far, I've used this code:

Measure =
VAR Test1 =
SUMX(
SUMMARIZE(
'Date table',
'Date table'[Date],
"TestVar",
[InputNumber]
),
"TestVar"
)

RETURN
CALCULATE(
IF(
Test1 > 0,
FIRSTDATE('Date table'[Date]),
""
)
)

When I change the input for values from [InputValue] to my measure in the first table presented, the dates with input values now instead show the date rather than [InputValue].

However, when I then remove the [Date] from the columns I don't get the expected first date like I presented in my ideal solution.

Let me know if I need to elaborate.

[amitchandak]

@Sperling , Prefer to use power query Unpivot

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

https://radacad.com/pivot-and-unpivot-with-power-bi

[Sperling]

Thanks for your response!

The data comes from a data cube, so this option isn't really ideal.

I just tried to simplify the issue, and would prefer to use dax for it.

[KNP]

@amitchandak is right. This sort of data modelling should be done in Power Query or further back, in the data source. Why do you think PQ isn't a good option?

If you insist on doing it in DAX, it will need to be a calculated column, not a measure. 

[Sperling]

I may have presented this in the wrong way.

The table I initally described is not a fact or dimension table, it is created in the Power BI report.

The zones come from one dimension and the dates from another, while the [InputNumber] is a calculated measure like I mentioned.
The dates are already a single column in the data, but I just presented it like this to illustrate the issue and preferred solution.

Heres the initial table I described, where the values have been replaced with the dates using the measure I wrote the DAX for above.

When I remove the dates from columns, I get this with the measure described above.

What I want here is to show 02/12/2022 for the second line, 05/12/2022 for the third line and so on. I don't even know why the 5th and 6th line shows 29/11/2022.

[KNP]

Thanks for the extra info, that makes more sense.

I think it would be much easier to come up with a solution if you could provide a link to an example PBIX. I realise you'd need to create it with dummy data but it would likely get you an answer much quicker. 

@amitchandak is much better at DAX than I am, so I'll see if I can bring him back into the conversation to see if he has a quick answer with the extra info you provided.