Skip to content

Instantly share code, notes, and snippets.

@nsdevaraj
Last active October 25, 2023 10:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nsdevaraj/701f88dc17d0a1638fc922c55e4d86a9 to your computer and use it in GitHub Desktop.
Save nsdevaraj/701f88dc17d0a1638fc922c55e4d86a9 to your computer and use it in GitHub Desktop.
generate filter context dax query
FILTERCONTEXT =
VAR OutDateYear = CALCULATE ( IF ( ISCROSSFILTERED ( '[Dim] Date Calendar'[Date].[Year] ), CONCATENATEX(VALUES('[Dim] Date Calendar'[Date].[Year] ), '[Dim] Date Calendar'[Date].[Year] , "',' "), "ALL MEMBERS" ), ALLSELECTED ( '[Dim] Date Calendar' ) )
VAR OutDateMonth = CALCULATE ( IF ( ISCROSSFILTERED ( '[Dim] Date Calendar'[Date-Month] ), CONCATENATEX(VALUES('[Dim] Date Calendar'[Date-Month] ), '[Dim] Date Calendar'[Date-Month] , "',' "), "ALL MEMBERS" ), ALLSELECTED ( '[Dim] Date Calendar' ) )
RETURN
"{'[Dim] Date Calendar.[Date].[Year]' : ['" & OutDateYear&"'], '[Dim] Date Calendar.Date-Month' : ['" & OutDateMonth&"']}"
function generateDAXQuery(selectedFilters) {
try {
const queryParts = [];
const regexPattern = /[\s.[\]]/g;
const regexOutPattern = /[\s.\-[\]]/g;
let openBrkt = "[";
let closeBrkt = "]";
for (const [filterName, tableVal] of Object.entries(selectedFilters)) {
if (filterName.indexOf("]") !== -1) {
openBrkt = "";
closeBrkt = "";
}else{
openBrkt = "[";
closeBrkt = "]";
}
const tableName = "'" + tableVal + "'";
const filterExpression = `CONCATENATEX(VALUES(${tableName}${openBrkt}${filterName}${closeBrkt} ), ${tableName}${openBrkt}${filterName}${closeBrkt} , "',' ")`;
const outVariableName = `Out${filterName.replace(regexOutPattern, "")}`;
const outExpression = `CALCULATE ( IF ( ISCROSSFILTERED ( ${tableName}${openBrkt}${filterName}${closeBrkt} ), ${filterExpression}, "ALL MEMBERS" ), ALLSELECTED ( ${tableName} ) )`;
queryParts.push(`VAR ${outVariableName} = ${outExpression}`);
}
const returnExpression =
`RETURN
"{${Object.entries(selectedFilters)
.map(([filterName, tableVal]) => {
const filterNameWithoutSpace = filterName.replace(regexOutPattern, "");
return `'${tableVal + "." + filterName}' : ['" & ${
"Out" + filterNameWithoutSpace
}`;
})
.join(`&"'], `)}` + `&"']`;
return (
"FILTERCONTEXT =" +
"\n" +
queryParts.join("\n") +
"\n" +
returnExpression +
'}"'
);
} catch (err) {
return "";
}
}
const selectedFilters = {
"[Date].[Year]": ["[Dim] Date Calendar"],
"Date-Month": ["[Dim] Date Calendar"],
};
generateDAXQuery(selectedFilters);
{'[Dim] Date Calendar.[Date].[Year]' : ['2019',' 2020'], '[Dim] Date Calendar.[Date-Month]' : ['ALL MEMBERS']}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment