Conditional Formatting on a Matrix & More
I've been stuck on a problem for my work for a while. I've made progress, but I can't seem to find a prooper solution to my conditional formatting issue. In Microsoft Power BI, I want to setup a matrix visual to show when the Doc Num of a Contract is New, Open, or Closed using Conditional Formatting. This will be over the course of different Verisons of Data pulls, and separated in different Unit groups. I want to ensure that I can adjust the DAX so that Closed only appears after a Doc Num disappears (e.g. not showing Closed in the same version it last appears) My current table has 3 columns: Doc Num, String Version, Decimal number Unit, String Lastly, I want to apply the conditional formatting to the matrix itself. This is because for each version, the Doc Num may change from New, to Open to Closed. Here's my current code: ContDocument Status = VAR _CurrentVersion = SELECTEDVALUE(DataPage_Tables_Append1[Version]) VAR _DocNum = SELECTEDVALUE(DataPage_Tables_Append1[DAC Doc Num]) VAR _CurrentUnit = SELECTEDVALUE(DataPage_Tables_Append1[Unit]) VAR _LastVersion = CALCULATE( MAX(DataPage_Tables_Append1[Version]), FILTER( ALL(DataPage_Tables_Append1), DataPage_Tables_Append1[DAC Doc Num] = _DocNum && DataPage_Tables_Append1[Unit] = _CurrentUnit ) ) VAR _FirstVersion = CALCULATE( MIN(DataPage_Tables_Append1[Version]), FILTER( ALL(DataPage_Tables_Append1), DataPage_Tables_Append1[DAC Doc Num] = _DocNum && DataPage_Tables_Append1[Unit] = _CurrentUnit ) ) RETURN SWITCH( TRUE(), _CurrentVersion = _FirstVersion, "New", _CurrentVersion > _FirstVersion && _CurrentVersion < _LastVersion, "Open", _CurrentVersion = _LastVersion, "Closed", BLANK() ) This is what the Output currently looks like: The problem is, that almost all Doc Nums are in Closed status at the last Version (unless the Doc Num is New during that Version), and that is not actually true. Some of those Doc Nums should be in Open status because it is not confirmed that the Doc Num disappeared. Another functionality that I want to add to my matrix, is to have a Doc Num be reopened in case there is an edit, and the Doc Num must be revisied. I do not know how to do this. Any guidance on this issue would be appreciated. Thank you in advance! Best, tkerr98
issue with a simple DAX function using REMOVEFILTERS
Hi everyone, I’m experiencing an issue with a simple DAX function using REMOVEFILTERS, which behaves as expected when tested in a DAX query view (e.g., with summarizecolumns) but fails to clear filters when used in a table or matrix visual in Power BI Report Server Desktop (September 2024 release). Here are the details: I have a very basic measure that uses REMOVEFILTERS to clear specific filters on a column. While this works perfectly in the DAX query editor, the visual context (table/matrix) still applies the filters, ignoring REMOVEFILTERS. I also tried alternative functions like ALL and ALLSELECTED, but they do not work in the visual either.
Wrong data displayed on Report Server
In my powerbi file I have several calculated columns. My organization uses PowerBI 2021 September version. Problem I'm having is with this one in particular: I'm calculating difference in minutes between two datetime columns. Another column is created to assign ID ranges to time for instance if time difference is less than 30 minus I'll return 1 for its range. I have another ranges and labels table which is connected to my table on this calculated ID of 1 with corresponding text label "<30 min" that I'm using to display in a table visual. Wherever it's possible I'm using SWITCH statement. If I refresh the report (it's based on import query) on desktop ranges are assigned correctly and where it's calculated range is 1, it's getting assigned correct range but when the report is published to the server instead of "<30 min" it might display "4-8hours". Can anyone suggest what can be potentially wrong or how do I debug to find the issue?
Add parameter in query designer (query from Power BI Performance analyser)
Hi guys, I am trying to add a parameter in my report and add the parameter in the query designer module. The query was generated within Power BI via Performance analyser. I saw different posts on this issue but no clear solution. First I have tried to create a parameter in the report (in my dataset properties) and add it with @ in my query such as // DAX Query DEFINE VAR __DS0FilterTable = TREATAS({@Test}, 'FACT - Conflicts'[Conflict category]) However, I have an error message that the parameter test was not declared. So I declared it again through "Query Parameters" module within the query designer (same name and another name). This time I can execute the query but it returns no row with a default value "Group" (which should not be the case - if I had put TREAT({"Group"}, it returns some rows). I tried to see what were the values in the Parameters properties window and I can see this. Which I don't understand because "Test" is not the name of my dataset.. Any idea to resolve this? Thanks a lot in advance!
