Forum Discussion

anandfarmers's avatar
anandfarmers
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Notebook with sempy - giving a date range for filters

Hi All,   I have sempy working in Notebook and can pass filters as explained in Microsoft documentation. filters = {     "TestData[Prodct]": ["ABC1"]     #,"State[State]": ["FLORIDA", "NEW YORK...
Data Science
power bi
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi anandfarmers ,

    Apologies for the delay in reply from our side. 

    fabric.evaluate_measure has the limitation of the filter that only suppors the 'in' operator.
    If you are looking towards range operation then i recommed to use fabric.evaluate_dax .

    For eg:

    import sempy.fabric as fabric

df_dax = fabric.evaluate_dax(
    "Customer Profitability Sample",
    """
    EVALUATE SUMMARIZECOLUMNS(
        'State'[Region],
        'Calendar'[Year],
        'Calendar'[Month],
        'State'[State],
        FILTER('Calendar', 'Calendar'[Year] >= 2009 && 'Calendar'[Year] <= 2011),
        )
    """)

df_dax


    Inorder to get some idea please refer below link,
    Read data from semantic models and write data that semantic models can consume - Microsoft Fabric | Microsoft Learn
    FILTER function (DAX) - DAX | Microsoft Learn

    Hope this is helpful. Please let me know incase of further queries.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi anandfarmers ,

Thanks for using Fabric Community.
Can you please check if below code works for you, it can get data filter by month and year.

 

 

import sempy.fabric as fabric

filters = {
    "State[Region]": ["East", "Central"],
    "State[State]": ["FLORIDA", "NEW YORK"],
    "Calendar[Month]": ["Aug"],
    "Calendar[Year]": [2013]
}

df_measure = fabric.evaluate_measure(
    "Customer Profitability Sample",
    "Total Revenue",
    ["Customer[State]", "Calendar[Date]", "Calendar[Month]", "Calendar[Year]"],
    filters=filters)

df_measure

 

 

 

Hope this is helpful. Please let me know incase of further queries.

  • anandfarmers's avatar
    anandfarmers
    Icon for Advocate I rankAdvocate I
    2 years ago

    Hi Anonymous ,

    Thanks for the reply.

    I understand we can give month name.

    But as I mentioned, if the date range starts part of a month and ends part of the same month or another  month then we have to list each date. e.g. 2nd of Jan 2023 to 5th of March 2023.

     

    I am trying to understand how to give such date ranges.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi anandfarmers ,

      Apologies for the delay in reply from our side. 

      fabric.evaluate_measure has the limitation of the filter that only suppors the 'in' operator.
      If you are looking towards range operation then i recommed to use fabric.evaluate_dax .

      For eg:

      import sempy.fabric as fabric

df_dax = fabric.evaluate_dax(
    "Customer Profitability Sample",
    """
    EVALUATE SUMMARIZECOLUMNS(
        'State'[Region],
        'Calendar'[Year],
        'Calendar'[Month],
        'State'[State],
        FILTER('Calendar', 'Calendar'[Year] >= 2009 && 'Calendar'[Year] <= 2011),
        )
    """)

df_dax


      Inorder to get some idea please refer below link,
      Read data from semantic models and write data that semantic models can consume - Microsoft Fabric | Microsoft Learn
      FILTER function (DAX) - DAX | Microsoft Learn

      Hope this is helpful. Please let me know incase of further queries.

      • anandfarmers's avatar
        anandfarmers
        Icon for Advocate I rankAdvocate I
        2 years ago

        Thank you for the reply.

        It calrifies different use cases of the functions.