JSON to DAX

[Atf23]

Hi all,
I want to find a dynamic solution that allows me to convert a json description of a filter to a dax measure.
This is an example of the json description of filters to apply:

{
"Filter001": [
{"Name": "Country", "Type":"include", "Value": "Spain"},
{"Name": "Region", "Type":"exclude", "Value": ["West", "North"]}
]
"Filter002": [
{"Name": "Country", "Type":"include", "Value": "Spain, Italy"},
{"Name": "Gender", "Type":"exclude", "Value": "Male"}
]
"Filter003": [
{"Name": "Age", "Type":"include", "Value": ">50"}
]
}
In the report, I want to offer a slicer where users can select the filter to apply: Filter001, Filter002, or Filter003 and when selecting the filter I want to display the filtered data in table visual.

For example, if a user selects "Filter001", only the rows having Country = "Spain" and Region <> "West" and Region <> "North" are displayed. 

Note. I cannot know in advance on which column the filters are specified. That's why, I need a dynamic conversion. I cannot use bookmarks, slicers or any static options because there are too many columns on which a filter can be specified.

Thanks a lot for any input

[AnalyticsWizard · Accepted solution]

@Atf23 

Creating dynamic DAX measures based on JSON input requires a dynamic and complex solution, which may not be fully supported by Power BI directly. Power BI doesn't have native functionality to parse JSON and dynamically generate DAX expressions based on that. However, there are a few approaches that could be taken, albeit with limitations and complexity.

Here's a conceptual approach using a combination of Power Query to parse the JSON and generate a table, and DAX to apply the selected filter:

1. Parse JSON in Power Query: Import the JSON string into Power BI using the Power Query editor. Parse it to create a table that lists each filter, its conditions, and values.

2. Create a Slicer Table Create a table with a distinct list of filter names (Filter001, Filter002, etc.) that users can use to select filters.

3. Create Relationships: Ensure that the tables created from the JSON parsing have relationships with your main data table.

4. DAX Measure for Filtering: Write a DAX measure that uses the selected filter from the slicer to apply the filtering to your data. You would typically use `SELECTEDVALUE` to get the current filter selection and `FILTER` or `CALCULATETABLE` to apply the conditions.

Here's a rough DAX example based on your requirements:

Dynamic Filter =
VAR CurrentFilter = SELECTEDVALUE('FilterSelection'[FilterName])
VAR IncludeCountry = LOOKUPVALUE('ParsedFilterTable'[Value], 'ParsedFilterTable'[FilterName], CurrentFilter, 'ParsedFilterTable'[Name], "Country", 'ParsedFilterTable'[Type], "include")
VAR ExcludeRegion = FILTER(
'ParsedFilterTable',
'ParsedFilterTable'[FilterName] = CurrentFilter && 'ParsedFilterTable'[Name] = "Region" && 'ParsedFilterTable'[Type] = "exclude"
)
VAR FilteredTable =
CALCULATETABLE(
'MainDataTable',
'MainDataTable'[Country] = IncludeCountry,
NOT('MainDataTable'[Region] IN EXCLUDEREGION[Value])
)
RETURN
IF(
ISFILTERED('FilterSelection'[FilterName]),
COUNTROWS(FilteredTable),
COUNTROWS('MainDataTable')
)

This DAX measure is highly conceptual and would need to be adapted to your specific model and tables generated from the JSON parsing.

The complexity of such an approach, especially in handling multiple filter types and conditions, means you would likely need to use an external tool or programming language to parse the JSON and generate DAX expressions or pre-computed tables that could be imported back into Power BI. Python scripting within Power BI might help automate part of this process, but it's still not straightforward.

This process would be significantly easier if there were a set number of known columns that could be filtered on, as you could then pre-define the DAX measures for filtering, but as per your requirement, this seems not to be the case.

Because of these complexities, if possible, I would recommend re-evaluating the requirements to see if there is any way to simplify or pre-define the filters to some extent.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍