Forum Discussion

eg1230's avatar
eg1230
New Member
4 months ago
Solved

Get first date a value from another column exists

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?!

  • FBergamaschi's avatar
    FBergamaschi
    4 months ago

    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

     

     

3 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    4 months ago

    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

     

     

  • pcoley's avatar
    pcoley
    Super User
    4 months ago

    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?



  • v-echaithra's avatar
    v-echaithra
    Community Support
    4 months ago

    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.