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)
Auto-Filter and Auto-Sort Matrix by Selected Parameter
Hi everyone, I have created a panel of 3 buttons using a Field Parameter called "FinancialFocus". This button selection returns the desired values in the following Matrix. If ORDERS is chosen, then ORDERS detail for each project is shown. If REVENUE is selected, then REVENUE figures are presented. This all seems to work as expected. The following "Top N" (i.e. top 75) filter is applied to this matrix: The following fields are used to generate the Matrix: "FinancialFocus" is a field parameter with the following definitions. Where, for example, "measure_REV_USD_d" is defined as follows, which essentially returns the year-to-date Revenue Actuals, Forecast, and Budget for each Project Description in the matrix above. "measure_ORDER_USD_d" and "measure_GP_USD_d" behave similarly. measure_REV_USD_d = CALCULATE(SUMX('OSGM Data_LC','OSGM Data_LC'[REV_USD_d]),AND(LEFT('OSGM Data_LC'[Period],1)="M",'OSGM Data_LC'[Period]<='OSGM Data_LC'[ctrl_ActThruPeriod])) What I'm trying to achieve is the ability to "auto-sort" and "auto-filter" the matrix based on the button selection. If ORDERS is selected, then FILTER the Matrix to show the Top 75 projects based on "Sum of ORDER_USD_d" Then...SORT the Matrix in descending order base on the ORDER Values shown in the Matrix If REVENUE is selected, then FILTER the Matrix to show the Top 75 projects based on "Sum of REV_USD_d" Then...SORT the Matrix in descending order base on the REVENUE Values shown in the Matrix If GROSS PROFIT is selected, then FILTER the matrix to show the Top 75 projects based on "Sum of GP_USD_d" Then...SORT the Matrix in descending order base on the GROSS PROFIT Values shown in the Matrix I attempted to use this solution from rpinxt, but the result I get is a bunch of 1's and other odd numbers. I feel his/her solution is along the lines of what I'm looking for - so I'm sure missing something. Here is my attempt at implementing rpinxt's solution: Added sort column to the Field Parameter table Created a Measure called "FinancialFocus_RANK" NOTE: I believe this is where I'm failing - I don't think I'm using RANKX and ALLSELECTED properly. Added "FinancialFocus_RANK" to the values well of the Matrix The result is mostly "1's" and a couple instances of "2966". Obviously not the result I'd want. Instead I'd like to see the first row of the matrix be a high value, and then incrementally get smaller as you scan through the matrix. Likewise, I'd like these rankings to change dynamically depending on the button selected (ORDERS, REVENUE, GROSS PROFIT), and I'd like the Top N Filter to respond in kind. Hopefully someone can point me in the right direction - thanks in advance!!! J
