Get first date a value from another column exists

[eg1230]

Hi,

I am trying to normalize a timeseries.

I made a simple example I think if I understand the  follwing problem I am closer to write the DAX by myself.

This is my data model: 

I have a slicer on master_f and oen on master_b to select exactly one value.

This is my DAX: 

FirstDatef = VAR SelectedCat = SELECTEDVALUE(master_f[cat])
RETURN 
    CALCULATE(
        MIN(f[date]),
        REMOVEFILTERS(f[date]),
        f[cat] = SelectedCat
    )

I dont understand why FirstDatef "is grwoing" together with date when shown in a matix:

My understanding is that all filters are removed, so the min date should alway be the same?!

[v-echaithra]

Hi @eg1230 ,

Thanks for reaching out to Microsoft Fabric Community.

Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.

Also to assist better, could you share sample data or sample .pbix file without any sensitive information. You can upload your files to any cloud storage like OneDrive or GoogleDrive and paste the link here with appropriate permissions.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[pcoley]

@eg1230 
1. Trying to understand the filter context: the dates field used in the rows of the matrix object comes from the b or the f table?
2. Can you have a date table with relationships (1-to-many) with both transactional tables (b and f)?
3. can you advoid the many-to-many bidirectional relationship between b and f?
4. which are the fields used in each relationship?

[FBergamaschi · Accepted solution]

Hi @eg1230 

there a couple of things that make the answer difficult

1 - you should use a calendar table as a dimension

2 - it seems to me you have a relationship between two facts, you should never connect facts between them but connect them through dimensions

3 - you seem to have the automatic date time active, it is a best practice to deactivate it (so you use a calendar table dimension)

Can you share the pbix so I fix a couple of things and maybe at the same time I solve the measure issue?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI