Forum Discussion

Einomi's avatar
Einomi
Icon for Helper V rankHelper V
4 years ago
Solved

Conditional Filter

Hello,   I have a structured table in Excel recording sales, and naturally I have a column date I have another table called DateMin I have another table called DateMax   I have three queries in...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • BA_Pete's avatar
    BA_Pete
    4 years ago

     

    Ah, yes, that makes sense. My bad.

    This is always going to be a cyclic reference as you're applying this as a step in your fact table.

    Revert your DateMin/Max queries back to how they were originally, then try this as your final fact table step:

    Table.SelectRows(
    #"Personnalisee ajoutee",
    each [Date] >= (DateMin ?? #date(1900,01,01))
        and [Date] <= (DateMax ?? #date(2999,12,31))
)

     

    Pete

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

Hi Einomi ,

 

I'd make the edit in your DateMin/Max queries to make usage in the fact table easier.

How are your DateMin/Max queries currently calculated?

 

Pete

  • Einomi's avatar
    Einomi
    Icon for Helper V rankHelper V
    4 years ago

    Hi BA_Pete 

     

    Thanks

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
    StartDate = ChangedType{0}[StartDate]
in
    StartDate

     

    this is how my two queries DateMin and DateMax are calculated.

    I have one query for DateMin (above) and one for DateMax

    Once I have loaded the table to PQ I have justt drilled down the value

    • BA_Pete's avatar
      BA_Pete
      Icon for Super User rankSuper User
      4 years ago

       

      Ok. So DateMin and DateMax are essentially manually entered in the workbook. No problem.

      Try this in your DateMin/Max queries:

      let
    Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
    varDate = ChangedType{0}[StartDate],
    StartDate = if varDate = null then List.Min(yourFactTable[DateColumn]) else varDate
in
    StartDate

       

      In your EndDate query, just change StartDate for EndDate in the varDate variable, and List.Min for List.Max in the StartDate step.

       

      Pete