Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rachaelwalker
Resolver III
Resolver III

Pulling Dates from another table with IF statement

I need help with a formula. I have two tables. My Quote table needs to pull the Expected_Close_Date from my Opportunity table based on matching Opportunity IDs. It has a one-to-many relationship because quotes have multiple products that go under the same opp. I need to pull the Expected_Close_Date for all items on the quote when OppID = OppID

 

Opportunity Table (1)

OppID

Expected_Close_Date

 

Quote Table (many)

QuoteID

ProductID

OppID

 

This will at least get me started, but I am also finding some have blank dates. If the Expected_Close_Date is blank, how do I tell it to populate a native date field that is already in the Quote table. Thank you!

 

1 ACCEPTED SOLUTION
ebeery
Solution Sage
Solution Sage

Hi @rachaelwalker ,

How about something like:

Expected_Close_Date (in Quote Table) =
VAR _Expected_Close_Date =
    LOOKUPVALUE (
        'Opportunity Table'[Expected_Close_Date],
        'Opportunity Table'[OppID], 'Quote Table'[OppID]
    )
VAR _Result =
    IF (
        ISBLANK ( _Expected_Close_Date ),
        'Opportunity Table'[insert some date field here],
        _Expected_Close_Date
    )
RETURN
    _Result



View solution in original post

4 REPLIES 4
KNP
Super User
Super User

If you want a Power Query solution...

 

Opportunity

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUVKK1YlWMgIyjEz1DY30jQyMDMFCxiAhAxQhE7DyWAA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [OppID = _t, ExpectedCloseDate = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"OppID", Int64.Type}, {"ExpectedCloseDate", type date}}
  )
in
  #"Changed Type"

 

Quote with merged Opportunity data and NewDate added

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSQWB9QyN9IwMjQ6VYnWglI6ioEYaMMVTGGEPGBCpjgioTCwA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [QuoteID = _t, ProductID = _t, OppID = _t, Date = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"QuoteID", Int64.Type}, {"ProductID", Int64.Type}, {"OppID", Int64.Type}, {"Date", type date}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Changed Type",
    {"OppID"},
    Opportunity,
    {"OppID"},
    "Opportunity",
    JoinKind.LeftOuter
  ),
  #"Expanded Opportunity" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Opportunity",
    {"ExpectedCloseDate"},
    {"ExpectedCloseDate"}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Expanded Opportunity",
    "NewDate",
    each if [ExpectedCloseDate] = null then [Date] else [ExpectedCloseDate],
    type date
  )
in
  #"Added Custom"

 

Paste each of these into the advanced editor of blank queries if you prefer to visulise it that way.

I hope this helps.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
ebeery
Solution Sage
Solution Sage

Hi @rachaelwalker ,

How about something like:

Expected_Close_Date (in Quote Table) =
VAR _Expected_Close_Date =
    LOOKUPVALUE (
        'Opportunity Table'[Expected_Close_Date],
        'Opportunity Table'[OppID], 'Quote Table'[OppID]
    )
VAR _Result =
    IF (
        ISBLANK ( _Expected_Close_Date ),
        'Opportunity Table'[insert some date field here],
        _Expected_Close_Date
    )
RETURN
    _Result



Hi @ebeery , @KNP ,
In the above problem statement if the relationship has been many-many & I need to pull the text column (e.g. Person name) instead of Expected close date then what dax approach should we take?

Thaks in Advance,

This worked! Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.