cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mak82pk Regular Visitor
Regular Visitor

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

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Power BI JavaScript filter between 2 dates

@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

6 REPLIES 6
mak82pk Regular Visitor
Regular Visitor

Re: Power BI JavaScript filter between 2 dates

I also tried GreaterThan and LessThan operators

Moderator Eric_Zhang
Moderator

Re: Power BI JavaScript filter between 2 dates

@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

Highlighted
mak82pk Regular Visitor
Regular Visitor

Re: Power BI JavaScript filter between 2 dates

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 Regular Visitor
Regular Visitor

Re: Power BI JavaScript filter between 2 dates

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

 

mak82pk Regular Visitor
Regular Visitor

Re: Power BI JavaScript filter between 2 dates

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

Moderator Eric_Zhang
Moderator

Re: Power BI JavaScript filter between 2 dates

@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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 208 members 1,638 guests
Please welcome our newest community members: