I worked through a few variations, and eventually ended up using the DAX Function ISFILTERED.Įmployee Sales Region Selection = IF( ISFILTERED(‘Employee Details'), SELECTEDVALUE(‘Employee Details', “No Selection”), “No Selection”) Filter Issue Number Two Some additional business logic would need to be applied to it, to FIRST check for active filtering BEFORE running the Selected Values logic. Selected Values doesn’t check to see if the column has an active filter on it, instead it’s only checking to see if there’s a single value from that column. I needed my DAX measure to run ONLY when there’s an ACTIVE filter on the column. It was triggering the Selected Value logic, because only a SINGLE VALUE was being given to it.įalse positive filter selection, despite no selection on the slicer: As an example, whenever I made a specific selection on Employee Sales Region, sometimes the filter selection for Employee Team Name would change too! What was happening is there was a Sales Region with only a SINGLE Team name. I quickly realized that I was getting false positive filter selections when using certain slicers. I thought that’s all I’d need, just rinse and repeat a bunch of Selected Values measures, and I’d be done! Not quite…I noticed a few issues after I did this. I figured I could use this function to point to my slicer columns, and return a “No Selection” alternative result if there wasn’t. For a thorough and in-depth article on it’s uses and abilities, I’ll refer you to a post Marco Russo wrote over at SQLBI. Selected Values is a fairly simple, and straight forward function. Here’s what the DAX Measure looks like for the Expense Ratio Value.Įxpense Ratio Value = SELECTEDVALUE(‘Expense Ratio', 0.50) Otherwise there’s an alternative result returned. single selection) on that column, it returns that value. Selected Values works like this, you give it a column reference, let’s say. In fact…if you look at my screenshots above, the Rolling Avg Months & Expense Ratio What Ifs are attached to Selected Values measures. Selected Values is used whenever you use the What If feature in Power BI Desktop. To get the right output with DAX I utilized one of the newer functions, Selected Values. The result works well, and uses a bit of clever DAX to always return the right selections, no matter the combination of selections among the slicers. They often took screenshots of this report, and pasted it into emails or slides to use in presentations. The BIGGEST reason the client wanted this, was for screenshots. The end result was a new section of the report, dedicated to calling out slicer selections. Same report with additional section for slicer filter selection: The end result looked something like this So, I essentially wanted to do something similar here, but to call out the filter selection for each slicer. I actually did something similar to this with Dynamic Titles when I posted about Power BI’s new Drill Through feature last year, that article can be found here. The beautiful thing about DAX, as mentioned in many articles on our site, is that it can easily return text values. The client and I brainstormed, and we decided to create card visuals to identify filter selections. Primary sections for – Headers, Slicers, Visuals/Tables:
ANDYROID BIT SLICER DOWNLOAD
Please click here to download the workbook to follow along. At the end of the day, we need to cater the report to the client’s needs. Truth be told though, it only partially matters what we think. Personally, I think a well-designed Power BI report has clear “enough” call-outs on the filters. Overall it comes down to this, data ambiguity can render a report A) Untrustworthy, B) Dangerous, or C) Useless. Now this is a scenario I’ve encountered a few times over the years, whether it’s been in Power BI, Excel, or other reporting tools. However, the client didn’t think the active filters in the report were easy to identify. I was working on a project for a client, and needed to create a Power BI report for them. I think it’s a scenario at least a few of you will relate to. Hello P3 Adaptive Nation, I’m excited to be back and writing a technical post again…it’s been too LONG! Today I want to talk to you about a real-world business problem and solution I encountered last year.