Hybrid BIUG May '23 | Field Parameters & Datamarts
Please join us at 17h00 on the 25 th of May for our next Power BI User Group event. The agenda is as follows: The What, How, and Why of Field Parameters - James McGillivray Bi News - Siya Zungu Datamarts in the PBI Service cont. - group collaboration session continued from March event Note: for odd-numbered months, such as January, March, May, etc., the event will be hybrid, meaning in-person and online. For more information, and to RSVP, please use this Meetup link: https://www.meetup.com/biug-sa/events/293098938/ As usual, pizza and refreshments will be served for those attending in person. If you missed the March event you can catch up on our YouTube channel here
Need help in using Field Parameter in Top N filter and Conditional Formatting
Hi, I have a report that has conditional formatting and Top N filter on a static measure named IRR and it works perfectly. However, I want to change the measure to dynamic and allow user to select among a few other measures like DPI, RVPI and TVPI. I have created a Field Parameter and add all measures to it. It works for the bar chart at the bottom. However, I couldn't get the conditional formating to work. Also, the Top N filter based on that field parameter is not working properly. Can you advise how can I fix them, please? Thanks in advance. URL of my Power BI Report: https://github.com/chris-kc-cheng/ftk-powerbi/blob/main/Private%20Markets.pbix
Need a Dax to make a measure for counting the rows on a dynamic table using 2 parameters [Power BI]
The Row count needs to be displayed on a Key card next to the table visual. The Data of the Table is from two Parameters (Multi-selection) : 'Selected Dimensions P3'[Select Dimensions] 'Selected Measures P3'[Select Measures] Rows on the Table keeps changing according to the field selection by the viewer. The row count on the key card should simultaneously change to the current number of rows displayed on Table Visual.
Hide categoris values if measures value less then 3
Hi, im new in power bi and i need to produce this result.... I have any measure aggregations that in certain scenarios ups to value of 3 (deceases, patients, fragile subjects and go on....) in that case i have not to show that but that if U see, the real category values 60-69 and 70-79 are replaced with an anoymous "N.I." (means Not Identified) And this is just a great challenge form me BUT i have another difficult problem to solve, because in my pbi report i have to activate fields parameter to offer a multi selection feature to user (in categories and also in measures). Because they have to be free to choose what to aggregate in that visual... can anyone helps me ? thanks at all folks! best regards Mauro
Change Field Parameter Names based on slicer selection
Hello, I have done a bit of searching but I couldnt find a solution or a workaround for this. Any help is much appreciated. I have created a Field Parameter Table with multiple measures. Each measure has a logic to calculate the sales and profit based on the selected month, say logic for 1st month of a Quarter - Jan, Apr, Jul, Oct is different from logic to calculate for 2nd month of a Quarter and so on. Now, the challenge is, based on the month selected from the slicers, I want the column name in the matrix to change. That is, if I select Jan or Apr or Jul or Oct, I want the parameter name "Month1 of Qtr Act" to be changed dynamically. I tried the SELECTEDVALUE('Calendar'[MonthName]) which doesnt work on Parameter Tables. Parameter = { ("Month1 of Qtr Act", NAMEOF('BPC_DAX'[Month_1_Q]), 0), ("Month2 of Qtr Act", NAMEOF('BPC_DAX'[Month_2_Q]), 1), ("Qtr_Implied", NAMEOF('BPC_DAX'[Qtr_Implied]), 2), ("Qtr_Estimated", NAMEOF('BPC_DAX'[Qtr_Estimated]), 3), ("Q1 Actuals", NAMEOF('BPC_DAX'[Q1_Actuals]), 4), ("Q2 Actuals", NAMEOF('BPC_DAX'[Q2_Actuals]), 5), ("Q3 Actuals", NAMEOF('BPC_DAX'[Q3_Actuals]), 6) } Thanks, Sandy
Error When Referencing Field Parameter in a Measure
I have a measure that dynamically returns a chart title based on slicer selection. Now I want to add to the measure a field parameter that will determine the chart title based on the slicer selections AND the field parameter selection. However, I get an error when trying to do so. Is it even possible to implement what I'm trying to achieve? Appreciate any tips please bhanu_gautam lbendlin 🙂 Note: the field parameter is referencing measures only and for each of the measures referenced, they too are referencing other measures. Hope that makes sense. Dynamic Chart Title = VAR FieldParameter = SELECTEDVALUE('Ethnicity Filter'[Ethnicity Filter]) VAR Slicer1 = SELECTEDVALUE('Gender and Ethnic Pay'[Business Unit 1]) VAR Slicer2 = SELECTEDVALUE('Gender and Ethnic Pay'[Business Unit 2]) VAR ChartTitle = IF( ISBLANK(FieldParameter), "ALL ETHNICITIES", FieldParameter & " REPRESENTATION" ) RETURN SWITCH( TRUE(), ISBLANK(Slicer1) && ISBLANK(Slicer2), "MPI - " & ChartTitle & " by TENURE", NOT ISBLANK(Slicer1) && ISBLANK(Slicer2), UPPER(Slicer1) & " - " & ChartTitle & " by TENURE", NOT ISBLANK(Slicer1) && NOT ISBLANK(Slicer2), UPPER(Slicer2) & " - " & ChartTitle & " by TENURE" ) //Field Parameter Ethnicity Filter = { ("European", NAMEOF('Measures - General'[European Rate]), 0), ("Other Ethnicity", NAMEOF('Measures - General'[Other Rate]), 1), ("Asian", NAMEOF('Measures - General'[Asian Rate]), 2), ("Māori", NAMEOF('Measures - General'[Māori Rate]), 3), ("Pacific Peoples", NAMEOF('Measures - General'[Pacific Rate]), 4), ("MELAA", NAMEOF('Measures - General'[MELAA Rate]), 5) }2 datasets in Paginated reports using parameters
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") (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", [TotalCostYTD], "ERVisitsYTD", [ERVisitsYTD], "MemberMonthsYTD", [MemberMonthsYTD], "Admits1000YTD", [Admits1000YTD],"AntidepressantMedicationAcutePhaseNumerator",[AntidepressantMedicationAcutePhaseNumerator],"AntidepressantMedicationAcutePhaseDenominator",[AntidepressantMedicationAcutePhaseDenominator],"AntidepressantMedicationAcutePhaseRate",[AntidepressantMedicationAcutePhaseRate],"AnnualWellnessExamNumerator" ,[AnnualWellnessExamNumerator],"AnnualWellnessExamDenominator",[AnnualWellnessExamDenominator], "AnnualWellnessExamRate",[AnnualWellnessExamRate],"ERVisits1000YTD", [ERVisits1000YTD], "BloodPressureNumerator", [BloodPressureNumerator], "BloodPressureDenominator", [BloodPressureDenominator], "BloodPressureRate", [BloodPressureRate], "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],"DAWNumerator",[DAWNumerator],"DAWDenominator",[DAWDenominator], "DAWFillRate",[DAWFillRate],"ERVisitsRate",[ERVisitsYTDMoreThan5Visits1000],"ERVisitsNumerator",[ERVisitsYTDMoreThan5Visits],"LowBackPainImaging18to64Numerator",[LowBackPainImaging18to64Numerator],"LowBackPainImaging18to64Denominator",[LowBackPainImaging18to64Denominator],"LowBackPainImaging18to64InverseRate",[LowBackPainImaging18to64InverseRate],"LowBackPainImaging65to75Numerator",[LowBackPainImaging65to75Numerator],"LowBackPainImaging65to75Denominator",[LowBackPainImaging65to75Denominator],"LowBackPainImaging65to75InverseRate",[LowBackPainImaging65to75InverseRate],"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],"April YTD","April 2024 YTD","Zero",0)
