Optional & Multi-Select Paginated Reports Query Parameters with Analysis Services Data Source
Hello Power BI community! I need help with parameters and paginated reports. I want to generate paginated reports from within Power BI using the Paginated Report Visual as the container. I'm using Analysis Services as a data source and need to employ DAX for the datasource. Since "report filter parameters" are slow, I prefer using "query parameters" for faster performance. My goal is to use Power BI slicers to send multi-select data and optional slicers from the Power BI page. This way, if a user doesn't make a selection, it defaults to "All." I'm struggling to find clear information or tutorials on implementing this technique. If you know of any tutorials creating parameters using DAX, please share. I appreciate any assistance. It seems that while Power BI incorporates SSRS, connecting them via parameters isn't as seamless as expected. Thanks!
sidebar that grows vertically (Align containers)
I am working on a power BI Paginated Report in Report builder: I have got 2 Rectanges (containers). The main container is in yellow. It contains 3 textboxes, a table and the red container. then there is the red container (which is inside the yellow container) that contains an image and a textbox in purple. I want the red container to be aligned with the yellow one. It needs to grow vertically and dynamically depending on the table and the textboxes sizes. As you can see circled in the image below the red container does not grow vertically and dynamically (it needs to fill the space circled in blue) alongside the table and textboxes with expressions. how can I make the red container the same height as the yellow one dynamically ? Basically my main goal for the red container is to act like a side bar. Keep in mind that I am using multiple datasets and I will use multiple tables. Thank you for your help in advance.Multi-Choice Filters within a Tabular Model in Report Builder problem
Can you help me filter multiple values in both PhysicianOrganization and then PCPLocation I am working on a Paginated report in REPORT BUILDER that the user would like to: Select date (Month/Year) then the PhysicianOrganization then the PCPLocation to have a pdf for that location and others only. (PhysicianOrganization must filter PCPLocation) Below the Physician Organization does not filter on the PCPLocation and it a must! Dataset1 will be filtered by PhysicianOrganization, PCPLocation and Date (monthly reporting) which used month and year and PhysicianLocation Dataset which has the Physician Organization & PCPLocation values which the Parameters connect to. Below is what the report data looks like and I added the DAX fields below (this is from a CUBE or Tabular connection) I am thinking I should put the Year and Month in the PhysicianLocation dataset? I have 3 datasets that do the following: Filter the PCPLocation by PhysicianOrganization but when I run my report I do not see data: I’ve 3 datasets: PO – First Filter PCPLocation – 2nd filter (I am using RSCustomDaxFilter(@ProvidersPhysicianOrganization,EqualToCondition) Main Dataset which has the month/year parameter My DAX Code is below: 4 Parameters 1) PO Parameter Referencing Query 1 1) EVALUATE DISTINCT ( SELECTCOLUMNS ( 'Providers', "PhysicianOrganization", 'Providers'[PhysicianOrganization] ) ) PCPlocation Dataset (2nd filter) 2) EVALUATE SUMMARIZECOLUMNS('Providers'[PCPLocation], RSCustomDaxFilter(@ProvidersPhysicianOrganization,EqualToCondition,[Providers].[PhysicianOrganization],String)) Above references Physician Organization Finally the main dataset where I THINK the issue MAY LIE: EVALUATE VAR FilteredTable = SUMMARIZECOLUMNS( 'Providers'[PhysicianOrganization], 'Providers'[PCPLocation], 'Patients'[InsuranceType], 'DateDim'[Year], 'DateDim'[MonthNameAbbreviation], FILTER( VALUES('DateDim'[Year]), 'DateDim'[Year] = VALUE(@Year) ), FILTER( VALUES('DateDim'[MonthNameAbbreviation]), 'DateDim'[MonthNameAbbreviation] = @Month ), FILTER( VALUES('Patients'[Populations]), 'Patients'[Populations] = "HAP HFHS Employees" ), FILTER( VALUES('Providers'[HFPN_GMCC_Flag]), 'Providers'[HFPN_GMCC_Flag] = "Yes" ), FILTER( VALUES('Outpatient'[OfficeVisitEncounter]), 'Outpatient'[OfficeVisitEncounter] = "Virtual Visit" ) ) RETURN ADDCOLUMNS( FilteredTable, "UniquePatients", [UniquePatients], "TotalCostYTD", [TotalCostYTD], "MemberMonthsYTD", [MemberMonthsYTD], "Admits1000YTD", [Admits1000YTD], "AnnualWellnessExamNumerator", [AnnualWellnessExamNumerator], "AnnualWellnessExamDenominator", [AnnualWellnessExamDenominator], "AnnualWellnessExamRate", [AnnualWellnessExamRate], "AntidepressantMedicationAcutePhaseNumerator", [AntidepressantMedicationAcutePhaseNumerator], "AntidepressantMedicationAcutePhaseDenominator", [AntidepressantMedicationAcutePhaseDenominator], "AntidepressantMedicationAcutePhaseRate", [AntidepressantMedicationAcutePhaseRate], "ERVisitsYTD", [ERVisitsYTD], "ERVisits1000YTD", [ERVisits1000YTD], "BloodPressureNumerator", [BloodPressureNumerator], "BloodPressureDenominator", [BloodPressureDenominator], "BloodPressureRate", [BloodPressureRate], "DAWNumerator", [DAWNumerator], "DAWDenominator", [DAWDenominator], "DAWFillRate", [DAWFillRate], "DiabetesHbA1cLessThan8Numerator", [DiabetesHbA1cLessThan8Numerator], "DiabetesDenominator", [DiabetesDenominator], "DiabetesHbA1cLessThan8Rate", [DiabetesHbA1cLessThan8Rate], "FuInNumerator", [FuInNumerator], "FuInDenominator", [FuInDenominator], "F/U Hospitalization for Mental Illness", [F/U Hospitalization for Mental Illness], "ReadmitsYTD", [ReadmitsYTD], "ReadmitsYTDRate", [ReadmitsYTD%], "AdmitsYTD", [AdmitsYTD], "AllowedCost", [AllowedCost], "AllowedCostYTD", [AllowedCostYTD], "AllowedPMPMYTD", [AllowedPMPMYTD], "BreastCancerScreeningNumerator", [BreastCancerScreeningNumerator], "BreastCancerScreeningDenominator", [BreastCancerScreeningDenominator], "BreastCancerScreeningRate", [BreastCancerScreeningRate], "ColorectalCancerScreeningDenominator", [ColorectalCancerScreeningDenominator], "ColorectalCancerScreeningNumerator", [ColorectalCancerScreeningNumerator], "ColorectalCancerScreeningRate", [ColorectalCancerScreeningRate], "ERVisitsRate", [ERVisitsYTDMoreThan5Visits1000], "ERVisitsNumerator", [ERVisitsYTDMoreThan5Visits], "VirtualNumerator", [OutpatientUtilYTD], "VirtualRate", [OutpatientUtil1000YTD], "DepressionScreeningNumerator", [DepressionScreeningNumerator], "DepressionScreeningDenominator", [DepressionScreeningDenominator], "DepressionScreeningRate", [DepressionScreeningRate], "DepressionPositiveScreenNumerator", [DepressionPositiveScreenNumerator], "DepressionPositiveScreenDenominator", [DepressionPositiveScreenDenominator], "DepressionPositiveScreenRate", [DepressionPositiveScreenRate], "CervicalCancerNumerator", [CervicalCancerNumerator], "CervicalCancerDenominator", [CervicalCancerDenominator], "CervicalCancerRate", [CervicalCancerRate], "GenericNumeratorYTD", [GenericNumeratorYTD], "GenericDenominatorYTD", [GenericDenominatorYTD], "GenericFillRateYTD", [GenericFillRateYTD], "LowBackPainImaging18to64Numerator", [LowBackPainImaging18to64Numerator], "LowBackPainImaging18to64Denominator", [LowBackPainImaging18to64Denominator], "LowBackPainImaging18to64InverseRate", [LowBackPainImaging18to64InverseRate], "September 2023 YTD", "September 2023 YTD", "Zero", 0 )Filtering on 3 Parameter in Paginated reports
I am working on a Paginated report that the user would like to Select date (Month/Year) then the Physician organization then the pcp location to have a pdf for that location only. Below the Physician Organization does not filter on the PCP Location. Dataset1 will be filtered by Physician Organization, PCPlocation and Date (monthly reporting) and Physician Location which has the Physician Organization & PCPlocation values which the Parameters connect to. Below is what the report data looks like and I added the DAX fields below (this is from a CUBE or Tabular connection) I am thinking I should put the Year and Month in the PCpOrganization table? EVALUATE VAR FilteredTable = SUMMARIZECOLUMNS( 'Providers'[PCPLocation], 'Providers'[PhysicianOrganization], 'Patients'[InsuranceType], 'DateDim'[Year], 'DateDim'[MonthNameAbbreviation], FILTER( VALUES('Patients'[Populations]), 'Patients'[Populations] = "GM" ), FILTER( VALUES('DateDim'[Year]), 'DateDim'[Year] = VALUE(@Year) ), FILTER( VALUES('DateDim'[MonthNameAbbreviation]), 'DateDim'[MonthNameAbbreviation] = @Month ), FILTER( VALUES('Providers'[PCPLocation]), 'Providers'[PCPLocation] = @PCPLocation ), FILTER( VALUES('Providers'[PhysicianOrganization]), 'Providers'[PhysicianOrganization] = @PhysicianOrganization ), FILTER( VALUES('Payment'), 'Payment'[PaymentType] = "Allowed" ), FILTER(VALUES('Providers'[HFPN_GMCC_Flag]), ('Providers'[HFPN_GMCC_Flag] = "Yes")), FILTER(VALUES('Outpatient'[OfficeVisitEncounter]), ('Outpatient'[OfficeVisitEncounter] = "Virtual Visit")), "UniquePatients", [UniquePatients], "TotalCostYTD", [TotalCostYTD], "MemberMonthsYTD", [MemberMonthsYTD], "Admits1000YTD", [Admits1000YTD], "AnnualWellnessExamNumerator",[AnnualWellnessExamNumerator],"AnnualWellnessExamDenominator",[AnnualWellnessExamDenominator], "AnnualWellnessExamRate",[AnnualWellnessExamRate], "AntidepressantMedicationAcutePhaseNumerator",[AntidepressantMedicationAcutePhaseNumerator],"AntidepressantMedicationAcutePhaseDenominator", [AntidepressantMedicationAcutePhaseDenominator], "AntidepressantMedicationAcutePhaseRate", [AntidepressantMedicationAcutePhaseRate], "ERVisitsYTD", [ERVisitsYTD], "ERVisits1000YTD", [ERVisits1000YTD], "BloodPressureNumerator", [BloodPressureNumerator], "BloodPressureDenominator", [BloodPressureDenominator], "BloodPressureRate", [BloodPressureRate], "DAWNumerator",[DAWNumerator], "DAWDenominator",[DAWDenominator], "DAWFillRate",[DAWFillRate], "DiabetesHbA1cLessThan8Numerator", [DiabetesHbA1cLessThan8Numerator], "DiabetesDenominator", [DiabetesDenominator], "DiabetesHbA1cLessThan8Rate", [DiabetesHbA1cLessThan8Rate], "ReadmitsYTD", [ReadmitsYTD], "ReadmitsYTDRate", [ReadmitsYTD%], "AdmitsYTD", [AdmitsYTD], "AllowedCost", [AllowedCost], "AllowedCostYTD", [AllowedCostYTD], "AllowedPMPMYTD", [AllowedPMPMYTD], "BreastCancerScreeningNumerator", [BreastCancerScreeningNumerator], "BreastCancerScreeningDenominator", [BreastCancerScreeningDenominator], "BreastCancerScreeningRate", [BreastCancerScreeningRate], "ColorectalCancerScreeningDenominator", [ColorectalCancerScreeningDenominator], "ColorectalCancerScreeningNumerator", [ColorectalCancerScreeningNumerator], "ColorectalCancerScreeningRate", [ColorectalCancerScreeningRate], "ERVisitsRate", [ERVisitsYTDMoreThan5Visits1000], "ERVisitsNumerator", [ERVisitsYTDMoreThan5Visits], "VirtualNumerator", [OutpatientUtilYTD], "VirtualRate", [OutpatientUtil1000YTD], "DepressionScreeningNumerator", [DepressionScreeningNumerator], "DepressionScreeningDenominator", [DepressionScreeningDenominator], "DepressionScreeningRate", [DepressionScreeningRate], "DepressionPositiveScreenNumerator", [DepressionPositiveScreenNumerator], "DepressionPositiveScreenDenominator", [DepressionPositiveScreenDenominator], "DepressionPositiveScreenRate", [DepressionPositiveScreenRate], "CervicalCancerNumerator", [CervicalCancerNumerator], "CervicalCancerDenominator", [CervicalCancerDenominator], "CervicalCancerRate", [CervicalCancerRate], "GenericNumeratorYTD", [GenericNumeratorYTD], "GenericDenominatorYTD", [GenericDenominatorYTD], "GenericFillRateYTD", [GenericFillRateYTD], "LowBackPainImaging18to64Numerator",[LowBackPainImaging18to64Numerator], "LowBackPainImaging18to64Denominator",[LowBackPainImaging18to64Denominator],"LowBackPainImaging18to64InverseRate",[LowBackPainImaging18to64InverseRate], "September 2023 YTD", "September 2023 YTD", "Zero", 0 ) VAR FilteredResult = FILTER( FilteredTable, [UniquePatients] <> 0 ) RETURN FilteredResult EVALUATE DISTINCT( SUMMARIZECOLUMNS( 'Providers'[PhysicianOrganization], 'Providers'[PCPLocation] ) )
Report Builder - Selecting All Values in Parameter but getting individual Reports "Collated"
Hello! I have a Paginated Report that I need to do something specific. Some details... I have 11 separate Datasets that all provide a piece of the final document. Each dataset corresponds to one table. The datasets are from the same datasource but they are not linked together in any star model or anything like that. They stand alone. The main dataset gives me two parameters, a "Team" dropdown and an "Employee" dropdown. Select the team, get only the employees for said team. Easy peasy, works great. Based on the employee selection, I filter the other datasets for that employee number and if there is data in that dataset, it adds it, else that associated table gets turned invisible. And voila, my report. Now, I have a working document, however, I need to be able to "Select All" on the employee list and have all the reports generate for each employee, doc after doc, in addition to just picking one report. As it stands now, the name in the header just shows the first name in the list and all of these employees data is just throw together in one report. In the words of Cleveland Brown from Family Guy, "That's nasty". I need to be able to have each employees report generated one after another. So report emp 1, then report emp 2, etc. etc. What have I tried, you ask? Two things... I tried using a list box and move all my tables into there. However, the list box needs a DataSetName. I give it the main dataset name, but then every table I add in gets it's DataSetName changed to match and that breaks the report. I tried going into my DataSource and associating the tables together based on a common field. That works in the sense that I have no errors, but the data for whatever reason is all over the place and not with the correct values. I can tinker with the data source but I did not create it and I just have to deal with how it is set up. I really only change datatypes or make sure that results are correct. I am hoping that someone can help me out! Generic examples below (imagine that sales are from one dataset and taxes are from another, plus 8 to 10 more tables, but for simplicity, just 2 in the examples): Example 1 Team B is chosen and I want a report for each of the options in the drop down. Teams: Emps: Team A Emp 2 Team B Emp 4 Emp 5 Currently, I get something like this: Bad Report Emp 2 Total Sales: 2300 Total Taxes: 920 What I want: Report 1 Emp 2 Total Sales: 500 Total Taxes: 200 Report 2 Emp 4 Total Sales: 1500 Total Taxes: 600 Report 3 Emp 5 Total Sales: 300 Total Taxes: 120 Example 2: Team A chosen, select all: Teams: Emps: Team A Emp 1 Team B Emp 3 What I am getting: Bad Report Emp 1 Total Sales: 3800 Total Taxes: 1520 What I want: Report 1 Emp 1 Total Sales: 2600 Total Taxes: 1040 Report 2 Emp 3 Total Sales: 1200 Total Taxes: 480
Power BI Report Builder- Query Designer not showing all features
Hi, I'm new to Power BI, I'm trying to create/map the parameter in PBI Report Builder Qry Designer. I do not see many option/features as same as in the snippet attached which was taken from youtube. I'm using the latest version. How to get the same screen with all features. I need to pass a parameter to allow users to change the date. The query is throwing error when I pass the parameter name instead of date in the below condition condition => date = cast('text',formatdate('MM/dd/yyyy',To_Date('MM/dd/yyyy' , '08/01/2024'))) i tried with the different options as below, nothing is working 😞 @parametername '@parametername' '"¶metername&"' Your help will be highly appreciated.. My screen Expected Screen Thank You..
