topic Re: Notebook with sempy - giving a date range for filters in Data Science

Notebook with sempy - giving a date range for filters

anandfarmers — Tue, 09 Jan 2024 03:43:05 GMT

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?

Re: Notebook with sempy - giving a date range for filters

Anonymous — Tue, 09 Jan 2024 10:50:40 GMT

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.

Re: Notebook with sempy - giving a date range for filters

anandfarmers — Tue, 09 Jan 2024 22:27:15 GMT

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.

Re: Notebook with sempy - giving a date range for filters

Anonymous — Fri, 12 Jan 2024 15:38:10 GMT

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.

Re: Notebook with sempy - giving a date range for filters

anandfarmers — Mon, 15 Jan 2024 01:49:42 GMT

Thank you for the reply.

It calrifies different use cases of the functions.

Re: Notebook with sempy - giving a date range for filters

Anonymous — Tue, 16 Jan 2024 04:27:19 GMT

Hi anandfarmers ,

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