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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mak82pk
Helper I
Helper I

Power BI JavaScript filter between 2 dates

var Filter =
[
{
"$schema": "http://powerbi.com/product/schema#advanced",
"target": {
"table": "Query1",
"column": "Date"
},
"logicalOperator": "And",
"conditions":
[
{
"operator": "GreaterThanOrEqual",
"value": "2017-11-23T00:00:00.000Z"
}
,
{
"operator": "LessThanOrEqual",
"value": "2017-11-30T00:00:00.000Z"
}]
},
{
"$schema": "http://powerbi.com/product/schema#basic",
"target": {
"table": "Query1",
"column": "Hotel"
},
"operator": "In",
"values": ["Versace"]

}
];

// Embed configuration used to describe the what and how to embed.
// This object is used when calling powerbi.embed.
// This also includes settings and options such as filters.
// You can find more information at https://github.com/Microsoft/PowerBI-JavaScript/wiki/Embed-Configuration-Details.
var config = {
type: 'report',
accessToken: accessToken,
embedUrl: embedUrl,
id: reportId,
settings: {
filterPaneEnabled: false,
navContentPaneEnabled: true
}
};

// Grab the reference to the div HTML element that will host the report.
var reportContainer = document.getElementById('reportContainer');

// Embed the report and display it within the div container.
var report = powerbi.embed(reportContainer, config);


// Report.on will add an event handler which prints to Log window.
report.on("loaded", function () {
report.off("loaded");
report.getFilters()
.then(filters => {
return report.setFilters(Filter);
})
});

 

I am using the above code to filter the report to display data for 1 week only but it the condition 

{
"operator": "GreaterThanOrEqual",
"value": "2017-11-23T00:00:00.000Z"
}
,
{
"operator": "LessThanOrEqual",
"value": "2017-11-30T00:00:00.000Z"
}

 

 

IS NOT WORKING

 

 

 error-bi.JPG

 

 

 

I would be grateful if your provide the solution at the earliest.

 

Thanks in advance

 

1 ACCEPTED SOLUTION

@mak82pk

In the case "the line chart is filtering the data but the calendar is not getting updated", based on my test, if the filter is applied to your bussiness/transaction data, the "cross filter direction" should be "Both". In "Single" case, the behavior in your case is expected. In Single, you shall filter the calendar table instead.

Capture.PNG

View solution in original post

6 REPLIES 6
Eric_Zhang
Microsoft Employee
Microsoft Employee

@mak82pk

Per my test, you can try

 

var Filter = {
$schema: "http://powerbi.com/product/schema#advanced",
target: {
table: "Table1",
column: "Date"
},
logicalOperator: "And",
conditions: [{
operator: "LessThanOrEqual",
value: "2017-11-04 00:00:00"
},
{
operator: "GreaterThanOrEqual",
value: "2017-11-03 00:00:00"
}
]
}

The table and column names are case sensitive, please ensure that the names are correct in your case. Also, please enable the filter panel(filterPaneEnabled:true) for debugging. When the filter is applied you'll see something similar below in the filter panel.

Capture.PNG

 

By the way, in your case, you may remove the trailing Z in the date string, see Power BI Offsetting DateTime filter passed via Json Power BI Embedded

First of all thank you very much for providing me with the solution

 

bi.JPG

 

the line chart is filtering the data but the calendar is not getting updated

 

 

        var Filter =
            [
                
                {
                    $schema: "http://powerbi.com/product/schema#advanced",
                    target: {
                        table: "Query1",
                        column: "Date"
                    },
                    logicalOperator: "And",
                    conditions: [{
                        operator: "LessThanOrEqual",
                        value: "2017-11-30 00:00:00"
                    },
                    {
                        operator: "GreaterThanOrEqual",
                        value: "2017-11-23 00:00:00"
                    }
                    ]

                },
                {
                    $schema: "http://powerbi.com/product/schema#basic",
                    target: {
                        table: "Query1",
                        column: "Hotel"
                    },
                    operator: "In",
                    values: ["Versace"]

                }
                
            ];

 

 

bi2.JPG

 

I tried to apply filters on Power bi desktop as well.

 

Thanks once again for the help

 

I would be grateful if someone provides me with the solution.

@mak82pk

In the case "the line chart is filtering the data but the calendar is not getting updated", based on my test, if the filter is applied to your bussiness/transaction data, the "cross filter direction" should be "Both". In "Single" case, the behavior in your case is expected. In Single, you shall filter the calendar table instead.

Capture.PNG

First of all thank you very much for providing me with the solution

 

bi.JPG

 

the line chart is filtering the data but the calendar is not getting updated

 

 

        var Filter =
            [
                
                {
                    $schema: "http://powerbi.com/product/schema#advanced",
                    target: {
                        table: "Query1",
                        column: "Date"
                    },
                    logicalOperator: "And",
                    conditions: [{
                        operator: "LessThanOrEqual",
                        value: "2017-11-30 00:00:00"
                    },
                    {
                        operator: "GreaterThanOrEqual",
                        value: "2017-11-23 00:00:00"
                    }
                    ]

                },
                {
                    $schema: "http://powerbi.com/product/schema#basic",
                    target: {
                        table: "Query1",
                        column: "Hotel"
                    },
                    operator: "In",
                    values: ["Versace"]

                }
                
            ];

 

 

bi2.JPG

 

I tried to apply filters onPower bi desktop as well.

 

Thanks once again for the help

 

mak82pk
Helper I
Helper I

I also tried GreaterThan and LessThan operators

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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