Dax whiz needed - Power BI Bar Chart visual altered by slicers.

[RockandGrohl]

Strap in, this is going to require a couple paragraphs of explanation. I've *nearly* done it but I just need a bit of help getting over the line.

My company has Projects and each project can have between 1-5 stages. Each stage has an overall cost. The idea here is to do a cost comparison of all projects by letting the user select the stages using a multi-select slicer with 5 stages on it.

So let's say the user wants to see how the costs of our projects change from stage 1 through to stage 3. They select Stage 1, Stage 2, Stage 3 and leave 4 & 5 blank. I therefore want to see all the projects which have stage 1, 2 and 3 in the database. This means that valid projects will have Stage 1&2&3, or Stage 1&2&3&4, or Stage 1&2&3&4&5. The bar chart should only show the first 3 stages.

The way I've done it is to create a ProjectSummary table, which has a column called StageABCCode. This is a one-line-per-project table and if that Project has Stage 1, Stage 3 and Stage 4, the value in the StageABCCode column is "ACD". If it's got stage 2 and 5, then the value is "BE" and so on. I then use a measure to codify the slicer output, so if I user selects Stages 2, 3 & 4 then the SelectedABC measure outputs "BCD".

SelectedABC =
VAR Stage1= IF(CONTAINS(VALUES('Stages'[Stage]), 'Stages'[Stage], "Stage 1"), "A", "")
VAR Stage2= IF(CONTAINS(VALUES('Stages'[Stage]), 'Stages'[Stage], "Stage 2"), "B", "")
VAR Stage3= IF(CONTAINS(VALUES('Stages'[Stage]), 'Stages'[Stage], "Stage 3"), "C", "")
VAR Stage4= IF(CONTAINS(VALUES('Stages'[Stage]), 'Stages'[Stage], "Stage 4"), "D", "")
VAR Stage5= IF(CONTAINS(VALUES('Stages'[Stage]), 'Stages'[Stage], "Stage 5"), "E", "")
RETURN Stage1& Stage2& Stage3& Stage4& Stage5

This SelectedABC is then referred to in another measure which sums the cost for each project, by splitting the SelectedABC into its component letter codes, and filtering using multiple AND operators. Or at least that's the logic. A code of ACD should show me all projects that have A AND C AND D, therefore valid projects would be "ACD", "ABCD" and "ACDE" - I hope that makes sense.

Stage ABC Cost =
VAR SelectedABC = [SelectedABC]  -- The string of selected stages, e.g., "ACD"
VAR SelectedChars =
    ADDCOLUMNS(
        GENERATESERIES(1, LEN(SelectedABC)),
        "Character", MID(SelectedABC, [Value], 1)
    )
VAR ValidRows =
    FILTER(
        ProjectSummary,
        -- Ensure ALL characters in SelectedABC are present in StageABCCode
        COUNTROWS(
            FILTER(
                SelectedChars,
                SEARCH([Character], ProjectSummary[StageABCCode], 1, 0) > 0
            )
        ) = COUNTROWS(SelectedChars)
    )
RETURN
    SUMX(ValidRows, ProjectSummary[Total Cost (Original)])

This works, I've checked the figures in the bar chart and they all line up.

The problem is, it's including figures that aren't relevant in the stages that aren't selected.

As an example, if you filter the slicer for Stage 1 and Stage 3, which returns "AC", valid rows in the database are: "AC", "ABC", "ABCD", "ABCE", "ACD", "ACDE" which is perfect. However, the projects with a B, D or E cost are pulling through into the visual:

I've inserted an image, apologies for the redactions. Top left is the slicer where you can see option 1 and 3 selected. On the right you can see the coding for "AC", and you can see the costs in the chart, the figures for all of them are correct, but I just don't want B, D & E displayed

Obviously, the visual is dynamic so I can't apply a visual-level filter each time.


The solution?

I can't get ChatGPT to wrap its stupid robotic head round this, but in my eyes a solution would be to explicitly overwrite the values of the Sum where the slicer isn't selected.

So in this case, A & C costs are £58m & £75m respectively. So I would want the code to run 5 IF statements that examine the status of the slicer selections and then zero out any inapplicable selections.

For example, if I select Stage 1, 3 & 4 that results in ACD being the code. Everything would sum as normal and then afterwards it would run 5 If statements resulting in True False True True False and if the statement is false it should zero out the cost.

Does anyone know how to make that possible?

Thank you so much!

 

[RockandGrohl]

I'm trying to think of alternate ways to do this, such as passing the slicer selections directly into the chart visual filter stage. ChatGPT also being unhelpful here.

 

[RoryA]

It's pretty hard to be specific without being able to see your data model or anything of any use in your picture, but it seems like you need a different measure to use in your SUMX. The new measure only returns ProjectSummary[Total Cost (Original)] if the stage is included in the slicer selection (you already have that logic) otherwise it returns BLANK().

 

[RockandGrohl]

Yeah it's difficult, I need to redact some stuff because of work, but I think I've covered everything that's needed.

Probably the easiest solution is to create a measure that reads the slicer selections & passes them into the visual level filter, but ChatGPT has no idea how to make it work, seems possible to me - I'm already reading the slicer selections and converting "Stage 1" into "A", "Stage 3" into "B", etc. So if I can produce a measure that outputs the names of the stages exactly then I can pass that into a filter.

I think the only caveat with that logic is that it would produce a string like "Stage1Stage3", instead of instructing the visual to filter on just Stage 1 AND Stage 3.


So perhaps the solution is to work out how to get the Slicer at the top & the Bar Chart visual to use the same Stage field, but whenever I do this, the numbers produced are completely out of whack, and I don't really know why.

The Slicer runs off a custom table with just the stage names on them. The bar chart visual uses the Stage field from the ProjectSummary table. The numbers produced are absolutely correct to the pound. As soon as I join them up something in the logic breaks and the numbers are all whiffy - and what's worse, it's still not filtering the **** bar chart correctly to only show me relevant stages!

 

[RoryA]

You never tell it to filter out the stages that aren't selected. You only filter for projects that have all the selected stages. That's why I said you need a new measure that returns the relevant total if the stage is one of the selected ones, and BLANK otherwise. You then use that new measure in your SUMX function above.