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.pbixNeed 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) }
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
Calculate if order is too late or on time based on parameter
Hi My datamodel has two tables called 'Indkøbsordrer' and 'Varemodtagelser'. The two tables are connected on [Ordre nøgle] in a one to many relationship. I'm trying to calculate if an order is "Too late" or "On time" based on the following DAX-formula. Ordrer Punktlighed = if( ISEMPTY( Filter( CALCULATETABLE( 'Varemodtagelser', ALLEXCEPT('Varemodtagelser','Varemodtagelser'[Indkøbsordrenummer])), sum(Varemodtagelser[Modtagelsesdato])-'Leveringstidsjustering'[Leveringstidsjustering Value]-sum(Varemodtagelser[Statistikdato]) > 0 ) ), "On time", "Late" ) The formula is working fine. But now I would like to distinguish between the value of the column 'Indkøbsordrer'[Indkøbsbilagsart]. If the value in the row is equal to "ZLM" or "ZCD, then use the parameter as is. But if the value is diffent, then use the value 5 days. So I wrote this formula, but it is not working, because when I use this measure in another measure to calculate the sum of "Delayed orders" I get 2.484. The same result as the original formula. Z_Ordrer_Punktlighed_Bilagsart = IF( ISEMPTY( FILTER( CALCULATETABLE( 'Varemodtagelser', ALLEXCEPT('Varemodtagelser', 'Varemodtagelser'[Indkøbsordrenummer]) ), SUM('Varemodtagelser'[Modtagelsesdato]) - IF( RELATED('Indkøbsordrer'[Indkøbsbilagsart]) = "ZLM" || RELATED('Indkøbsordrer'[Indkøbsbilagsart]) = "ZCD", 'Leveringstidsjustering'[Leveringstidsjustering Value], 5 ) - SUM('Varemodtagelser'[Statistikdato]) > 0 ) ), "On time", "Late" ) I hope someone will take a look at the formula and maybe help me correct it? Thanks in advance. Best regards MortenDefault values for field parameter with language support?
I've got a field parameter to which I've added language support using the method describere here: Synchronize multiple field parameters - Power BI | Microsoft Learn _p_location_resolution = { //english ("region", NAMEOF('dim terminal'[region]), 0, 1), ("country", NAMEOF('dim terminal'[country]), 1, 1), ("city", NAMEOF('dim terminal'[city]), 2, 1), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 1), //swedish ("region", NAMEOF('dim terminal'[region]), 0, 2), ("land", NAMEOF('dim terminal'[country]), 1, 2), ("stad", NAMEOF('dim terminal'[city]), 2, 2), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 2), //spanish ("región", NAMEOF('dim terminal'[region]), 0, 3), ("país", NAMEOF('dim terminal'[country]), 1, 3), ("ciudad", NAMEOF('dim terminal'[city]), 2, 3), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 3) } A problem with this is that if I add a language to my model but forget to update all field parameters there is no fallback. I use this field parameter in a slicer and If I filter for a language that is not supported by the field parameter the slicer becomes empty. My question is if it is possible to modify this in order to add a default definition? I tried letting ChatGPT be creative and it came up with this: _p_location_resolution = VAR SelectedLanguage = SELECTEDVALUE('languages'[ISO abbreviation], "en") RETURN SWITCH( SelectedLanguage, "en", { ("region", NAMEOF('dim terminal'[region]), 0, 1), ("country", NAMEOF('dim terminal'[country]), 1, 1), ("city", NAMEOF('dim terminal'[city]), 2, 1), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 1) }, "sv", { ("region", NAMEOF('dim terminal'[region]), 0, 2), ("land", NAMEOF('dim terminal'[country]), 1, 2), ("stad", NAMEOF('dim terminal'[city]), 2, 2), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 2) }, "se", { ("región", NAMEOF('dim terminal'[region]), 0, 3), ("país", NAMEOF('dim terminal'[country]), 1, 3), ("ciudad", NAMEOF('dim terminal'[city]), 2, 3), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 3) }, // default to english { ("region", NAMEOF('dim terminal'[region]), 0, 1), ("country", NAMEOF('dim terminal'[country]), 1, 1), ("city", NAMEOF('dim terminal'[city]), 2, 1), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 1) } ) But this gives the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". Unfortunately I'm too new to Power BI and DAX to determine if it's just a tiny fixable error or if I'm not allowed to write DAX code like this in a field parameter. I'm guessing that the field parameter definition is not reloaded when changing the language filter so it isn't possible to do something like this but hey, I got to ask.
