Notebook with sempy - giving a date range for filters

[anandfarmers]

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"]

}

df_measure = fabric.evaluate_measure(

    # Dataset name

    "FabricTraining-NewModelForUsers"

    # Measure

    ,"Sum of Price"

    # Group By

    ,["TestData[Prodct]"]

    # Filters

    ,filters=filters

)

df_measure

The above example work fine.

My question :

How can I pass a date range using this filters dictionary technique?

e.g. For Jan 2024 dates - do I need to list each date? 

That would make it dificult if the date range spans starting part of a month and ending part of another month.

Any  suggestions?

[v-gchenna-msft]

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]

Hi @v-gchenna-msft ,

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.

[v-gchenna-msft · Accepted solution]

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 | ...
FILTER function (DAX) - DAX | Microsoft Learn

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

[anandfarmers]

Thank you for the reply.

It calrifies different use cases of the functions.

[v-gchenna-msft]

Hi @anandfarmers ,

Glad to know that your query got resolved. Please continue using Fabric Community for your further queries.