multiple values in one cell, how to adjust to be able to filter

[fjjpeeters1976]

hi all,

I have downloaded data out of a project tracking tool.

I have a column that consists of multiple values. It shows in which year(s) a project is considered in the budget, this can be one or more years (see picture below). In the end I want to have a filter in power bi where the user can choose all projects that have been in budget in one or more years. In power query I do not want to create multiple lines for each project in fear of double counting financial impacts. If I would put these items in several columns how could I create a filter that looks in all of these? Is there a smart way to do this?

[v-sathmakuri]

Hi @fjjpeeters1976

Could you let us know if the response provided was helpful in resolving your issue?

Thank you!!

[v-sathmakuri]

Hi @fjjpeeters1976 ,

Could you please let us know whether the provided response helped in resolving the issue. 

Thank you!!

[Shahid12523]

Your column has multiple years in one cell, so slicer treats them as one value.

Best fix: In Power Query → split by delimiter → split to rows → you get (Project, Year) pairs. No double counting if you aggregate with measures.

Alternative (less ideal): create separate year flag columns or use a DAX search measure.

Recommended: split to rows and model properly.

[v-sathmakuri]

Hi @fjjpeeters1976 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MarkLaf  and @Greg_Deckler  for your response.

I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss more on this. 

Thanks!!

[MarkLaf · Accepted solution]

If you provide some test data (in a table that we can easily copy/paste into PBI), we can in turn provide solutions more tailored to your problem.

For an initial response, I'll use some test data that mimics your multi-"In Budget" issue (budget year range in this sample is 2020-2023):

Projects

Project Id	Project Name	In Budget
1	ABC	In Budget 2020; In Budget 2021
2	DEF	In Budget 2020; In Budget 2021; In Budget 2022; In Budget 2023
3	GHI	In Budget 2022; In Budget 2023
4	JKL	In Budget 2020; In Budget 2023
5	MNO

First, at top level, this is a modeling problem.

It will be helpful for this problem and on your path to Power BI expertise to review and understand data modeling. Here is a good starting point: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

And specifically, you are dealing with a many-to-many relationship. I.e., one project can have multiple associated In Budget Years, and one In Budget Year can have multiple associated projects. Modeling these M:M relationships correctly in Power BI is a core skill. See here for a starting point: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

Long story short, we should set up a second table to achieve what you are looking for. Our model will still also have our original table with "1 project, 1 row" format. This new table should look something like:

ProjectsInBudgets

Project Id	In Budget
1	In Budget 2020
1	In Budget 2021
2	In Budget 2020
2	In Budget 2021
2	In Budget 2022
2	In Budget 2023
3	In Budget 2022
3	In Budget 2023
4	In Budget 2020
4	In Budget 2023
5

This should be set up in Power Query. The steps would be: 1) reference Projects as a new query, 2) Split [In Budget] by semicolon delimeter into new rows and expand, and then 3) select just Project Id | In Budget columns. Here is the M for this if you want to just create a blank query and paste into advanced editor:

let
    Source = Projects,
    SplitBySemi = 
        Table.ExpandListColumn(
            Table.TransformColumns(
                Source,
                {{ "In Budget", Splitter.SplitTextByDelimiter("; ", QuoteStyle.None), type {text} }}
            ),
            "In Budget"
        ),
    SelectCols = Table.SelectColumns(SplitBySemi, {"Project Id", "In Budget"})
in
    SelectCols

To set up the slicer functionality you want, we'll have to load this data from Power Query and continue on in the semantic model layer.

Note: a M:M relationship is usually split into three tables, in our example they would look like Projects -1---M-> ProjectsInBudgets <-M---1- InBudgets. For our purposes against this problem, we don't really need the InBudgets dimension, so I'll skip it.

First, set up a 1:M relationship between Projects and ProjectsInBudgets (PBI may do this automatically). Your model view should look like:

You now have all the building blocks to put together the visuals you want (but not the full slicer functionality, which we'll cover at end). Go to report view and set up whatever visual you want and a slicer with ProjectsInBudgets[In Budget]. Something like:

At this point, the visual elements are there, but you'll notice that the In Budget slicer does not affect the Projects table. This is because Power BI only automatically enforces filtering from the 1 side of a relationship (Projects). To have filtering occur from the M side of the relationship (ProjectsInBudgets), we'll have to explicitly tell the Projects visual to filter based on ProjectsInBudgets.

We accomplish this with a measure filter.

The measure:

In Budget Filter = CONVERT( NOT ISEMPTY( ProjectsInBudgets ), INTEGER )

And the filter setup:

Now, selections in the ProjectsInBudgets[In Budget] slicer will filter the Projects table.

Note that adding selections to the In Budget slicer will broaden the filter (e.g., selecting 2021 and 2023 will give all projects that were in budget in 2021 OR 2023). This is default slicer behavior. From your post, though, it sounds like you may rather want AND slicer behavior (e.g., selecting 2021 and 2023 will give all projects that were in budget in 2021 AND 2023). You can achieve this with a different slicer filter, including below just in case.

In Budget Filter_AND Criteria = 
VAR _selectedYears =
    ALLSELECTED ( ProjectsInBudgets[In Budget] )
VAR _projectYears =
    CALCULATETABLE (
        VALUES ( ProjectsInBudgets[In Budget] ),
        REMOVEFILTERS ( ProjectsInBudgets ),
        VALUES ( Projects[Project Id] )
    )
VAR _allSelectedAreInProject =
    ISEMPTY ( EXCEPT ( _selectedYears, _projectYears ) )
VAR _slicerNotFiltered =
    NOT ISFILTERED ( ProjectsInBudgets[In Budget] )
RETURN
    CONVERT ( IF( _slicerNotFiltered, TRUE, _allSelectedAreInProject ), INTEGER )

Quick snip of different measure filters in action.

[Greg_Deckler]

@fjjpeeters1976 I highly recommend that you split the column and then unpivot creating multiple rows for each project. That said, if you are loath to do so, you can create a separate, disconnected table for your slicer and then couple that with a Complex Selector that checks the multiple columns that you have created and returns 1 or 0 if it is in the year or not. The Complex Selector - Microsoft Fabric Community