needhelp

45 Topics

Column not showing in DAX measure
Hi everyone, I’m running into a strange issue while creating a DAX measure. I have a table that includes a Year column, but when I try to use it inside a measure, the column does not appear in IntelliSense, and if I type it manually, the measure returns an error. Here is the expression I’m trying to write: DisplayItem = FORMAT(Table[Year], "0") & " — " & Table[Event However: Table[Year] doesn't show up in the field list inside the measure editor. When I type it manually, I get an error that the column cannot be found or used in this context. Other columns from the same table appear normally. I already checked the data type, refreshed the model, and validated relationships. Has anyone faced this before? Is there a specific reason why a column would not appear inside a DAX measure while it exists in the model? Any suggestions would be greatl y appreciated! ]
Solved
Nabha-Ahmed
8 months ago Place DAX Commands and Tips
1.7KViews
2likes
6Comments
Calculation groups to replace slow IF/SWITCH?
Hi All I would appreciate your help to understand whether this idea suggested by genAI is true or a hallucination. I have a report in which the main page has many KPIs and 2 modes related to period selection, preset and custom. Preset is for predefined periods (Last 7/14/30 Days). Custom allows the user to select any date range. The switch between the 2 modes is done via bookmarks. Each KPI is represented by 2 measures, one for preset dates and another for the custom. The bookmarks are basically showing preset version of the measures and hiding the custom version or vice versa. The preset mode KPI measures use precalculated data, which makes them fast. The custom mode KPI measures use non-precalculated data which makes them slower than the preset ones. Example of measures: 1. [Builds_Triggered_preset] is used in custom mode and uses raw data. 2. [Builds_Triggered_precalculated] is used in preset mode and uses precalculated aggregated data. In the beginning, we tried to avoid bookmarks by creating parent measure that would contain an IF/SWITCH function that would decide whether it should use precalculated data or not. Example: [Builds_Triggered]= IF(SELECTEVALUE(dimDate[Period]="Custom",[Builds_Triggered_precalculated],[Builds_Triggered_preset]) When we tried this, we faced performance issues where the slower time was always recorded even when the fast precalculation logic condition was fulfilled . We suspect the IF/SWITCH function was doing eager evaluation. A ticket to the Microsoft support team didn't help either... So the bookmarks are there now to show/hide the relevant version of the measures. But these bookmarks are causing an overhead in the development as they require additional maintenance and testing all the time. MS Copilot suggested that calculation groups instead can be used instead of IF/SWITCH thus avoid the eager evaluation. Is it really possible in this scenario? and how to do that? I had a very long conversation with Copilot and I feel I'm going in circles 😐
Solved
karimm
9 months ago Place Desktop
2.2KViews
0likes
10Comments
Get User Download Log From Sql Server
Dear all, Currenly we're running PBIRS on our local server. We would like to track our user's download log from the sql server. Example: TimeStamp UserName ObjectName TypeName Path IP 2024-08-10 Alex CustomerProfile PBIReport /DeptA/CustomerAll/Customer 175.75.10.51 2024-08-15 Alice Purchase_1M PBIReport /DeptA/PurchaseAll/Purchase_1M 175.75.25.55 2024-08-17 Amber PurchaseAll Folder /DeptA/PurchaseAll 175.75.30.52 Thanks! Best Regards, Alex
Solved
alexyin1053
1 year ago Place Report Server
1KViews
0likes
2Comments
Report Builder Paginated report w/ 2 datasets sharing common Parameters issue
I have a Paginated Report (I am using a tabular model from our analysis server) question as I have 2 datasets joined by a common Parameter which is the DateColumn MonthYear- I have these referenced inside both datasets like so: (dataset1 uses both parameters for filtering but I want dataset2 to only use the date filter) Dataset1 has YearMonth and PhysicianOrg parameters but Dataset2 only has YearMonth as we do not need the PhysicianOrg parameter for Dataset2. I have a total of 4 datasets from the same source. Dataset1 will be filtered by both Physician Organization and Date (monthly reporting) and DataSet2 will only be filtered by YearMonth. I created 2 new tables for both Parameters (YearMonth and PhysicianOrg). Dataset2 only references the yearMonth parameter which is what I want as I wish to COMPARE the OVERALL score to the score which will be filtered by both YEARMONTH and PHYSICIANORG. When I add a new table and reference dataset2 along with dataset1 in one table it does work. I tested this expression from Dataset2: =Lookup(Fields!YearMonth.Value, Fields!YearMonth.Value, Fields!ID_AllowedPMPMYTD_.Value, "DataSet2") AND =SUM(Lookup(Fields!YearMonth.Value, Fields!YearMonth.Value, Fields!AllowedPMPMYTD_2.Value, "DataSet2")) and it does NOT WORK. (the numbers are not matching the excel formula which is =IFERROR(CUBEVALUE("Cube",GM,CubeMeasures!$D$25,CurrentMonth),0). (the current month is hardcoded and referenced I tried this expression inside the same table and referenced Dataset2 but that did not work. I am trying to match an excel formula report which uses HFPN overall is just AllowedPMPMYTD =CUBEMEMBER("Cube","[Measures].[AllowedPMPMYTD]") (it filters by month using How is this matched in Report Builder??? Do I need to use a DAX variation of CURRENT MONTH? I use the formula below sliced by yearmonth (SQL via Tabular Model) in Report Builder: The question is how do I show both results from 2 datasets in one paginated report? I just need one column with the same expression to filter differently: The Rate Column Filters by both parameters and the HFPN column filters by yearmonth column. Any suggestions? I created dataset2 just for HPFPN overall which I am currently hard coding until I can work this relatively simple problem out. EVALUATE SUMMARIZECOLUMNS('Providers'[PCPLocation], 'Providers'[PhysicianOrganization],'DateDim'[Year],'DateDim'[MonthNameAbbreviation],'DateDim'[YearMonth], FILTER(VALUES('Providers'[PhysicianOrganization]), 'Providers'[PhysicianOrganization] = @PhysicianOrganization),FILTER(VALUES('DateDim'[YearMonth]), ('DateDim'[YearMonth] =@YearMonth)), FILTER(VALUES('Patients'[Populations]), ('Patients'[Populations] = "GM")), "UniquePatients", [UniquePatients], "TotalCostYTD",
Karolina411
2 years ago Place Report Server
1.3KViews
0likes
2Comments
dax removefilter not reconize in excel
Hi I created a dashboard in Excel with dax on my computer, it works well There is some pivot, and i have a measure like this one: mesure 1:=var nbmois =5 var CAN1p = CALCULATE([VENTES DE MARCHANDISES + PRODUCTION 2];REMOVEFILTERS(Exercices[Exercices]);Exercices[Exercices]="N-1 Pr") var CAN1 = CALCULATE([VENTES DE MARCHANDISES + PRODUCTION 2];REMOVEFILTERS(Exercices[Exercices]);Exercices[Exercices]="N-1") return SWITCH ( TRUE; min(Exercices[Exercices] ) = "N"; [Switch SIG Balance 2] / ( [VENTES DE MARCHANDISES + PRODUCTION 2] ); min(Exercices[Exercices] ) = "N-1"; [Switch SIG Balance 2] / ( [VENTES DE MARCHANDISES + PRODUCTION 2] ); min(Exercices[Exercices] ) = "Variation"; [Switch SIG Balance 2] /(CAN1*5/12) As you see i use REMOVEFILTERS I try to use this dashboard on a another computer, and REMOVEFILTERS isnt' reconize .... fist time i see that I try to remove and reinstall Office, same way If someone have a idea Thanks Nicolas
Solved
nicolast29
2 years ago Place DAX Commands and Tips
1.9KViews
0likes
2Comments
MS Gantt Chart Visual
I am using the MS Gantt chart visual. When I am creating in desktop, it interacts as it should with other visuals, but when I publish to my workspace to test it out, the interactions fail and the chart does not filter based on selections made within the other visuals. Can anyone provide some insight as to why?
Anonymous
2 years ago Place Custom Visuals Development Discussion
520Views
0likes
0Comments
Measure Monthly to diary.
Hi, I have a measure [A] where it gives total monthly value. Additionally, I have a measure [B] that calculates the daily value by dividing [A] by the total number of days in the month. However, regardless of the month selected, [B] always returns the value for the last month. What I want is a new measure that provides the sum of [B] for the selected date range. Here are the different measures. A =IF ( [IsValid], CALCULATE ( SUM ( 'table'[valueA] ), table[valueB] = "C" ), BLANK () ) ) B = VAR MonthlyValue = [A] VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month]) VAR SelectedYear = SELECTEDVALUE('Calendar'[Year]) VAR DaysInMonth = DAY(EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0)) RETURN IF( [IsValid], MonthlyValue / DaysInMonth, BLANK() ) Example with data. For example, if I select the date range from February 1, 2024, to April 5, 2024, I expect the result to be: If the daily value of feb is 5, march is 6 and apr is 7, the result would be 5*days in February(29)+6*days in March(31)+7*days in April(5 in that case). The result is 145+186+35=366 Any assistance would be greatly appreciated. Thank you! Any help would be good, thanks!
Solved
BMM27
2 years ago Place DAX Commands and Tips
2.2KViews
0likes
7Comments
hide a text box if a Tablix shows "NoRowsMessage" or has not rows in PBI report builder
Hi, In Power Bi report builder, I need to hide a text box if a tablix shows "NoRowsMessage" or does not have any rows. I've tried different options,none of them worked. I pasted them into Properties->Visibility->Hidden->Expression =IIF(CountRows("DataSet1") > 0, False, True) =IIF(CountRows(Fields!Title.Value, "DataSet1") < 0, True, False) Please tell me a solution.
OlegV
2 years ago Place Report Server
844Views
0likes
2Comments
Power BI measure
Hello Everyone, I am newbie to Power BI. I have a table with SREQ ID and Project ID. Please refer the below Image. When I apply the filter on Project ID that contains "E" or "G", it should give me the total count of SREQ IDs. Please refer below Image: Now I just wanted to create dax measure, so that it gives count of SREQ ID's as 3 in this case. Please note that I wanted to apply the filter on the visual, don't want to define it inside dax. Thank you
Solved
HPS1RT
2 years ago Place DAX Commands and Tips
1.5KViews
0likes
5Comments
Refresh Now button greyed out on Reporting Server
Hi. I'm new to this Reporting Server, mainly worked with PBI Service. I noticed the refresh now button is greyed out under the settings of auto refresh. Why is that so?
Solved
katto16
2 years ago Place Report Server
714Views
0likes
1Comment