Measure used as a Filter breaks another measure
Hi Guys, maybe someone will be able to help. So I have a measure in my visual, this is kind of statistical measure (Percentile), calculated on value surv_model_score where results are affected by slicers applied (in general, by applying slicers we decrease population volume -> number of rows*), so I had to use Allselected() function to include filtering dimensions in context. Here are my calcs: > (* - this measure makes it) count_rows_survival= countrows(ALLSELECTED(act_source[Signature team ID],act_source[Signature Team Name], act_source[FA Name], act_source[fa_demo], act_source[Distance],act_source[Average NYSE LoS], act_source[Average AuM],act_source[Submitted_Bio_Team])) > rank_survival= RANK( DENSE, ALLSELECTED(act_source[Signature Team Name], act_source[surv_model_score], act_source[FA Name], act_source[Distance],act_source[Average NYSE LoS],act_source[fa_demo], act_source[Average AuM],act_source[Submitted_Bio_Team]), ORDERBY(act_source[surv_model_score], asc, act_source[Signature Team Name], asc)) > surv_percentile = DIVIDE([rank_survival_fa-team_tests]-1, [count_rows_survival_fa-team_test]-1,0) > Long Lasting Rating = IF((if((round(5-([surv_percentile] * 4),0))>=4,1,0))=1,"★"," ") Everything works fine, snapshot of data below then I wanted to filter by these measure Long Lasting Rating by using it in slicer, however using measures in slicers is not possible. So I have followed instructions in this video. Below are calcs built basing on video > LLR_COMMUNITY_check_ = IF([Long Lasting Rating]="★","Promoted","Not Promoted") > LLR_COMMUNITY_filter = var x1=SELECTEDVALUE(LLR_MANUAL_SLICER[LLR Status]) return IF(ISBLANK(x1),1,IF([LLR_COMMUNITY_check]=x1,1,0)) + table "LLR_MANUAL_SLICER created with values as following: Everything is fine, until last step when I put my new measure into vizual filter and set as "1", then my Long Lasting Rating get broken and each record has a "star" which is incorrect state. Am I missing something? Unfortunately I cannot put LLR_COMMUNITY_filter measure in Allselected() function what potentially would fix Long Lasting Rating calc. BTW filtering works perfectly fine, so when I choose "Promoted" from a slicer only records originally having "star" are displayed rest are filtered out. Issue is when no selection in "Promoted/Not Promoted" slicer is done or if I select "Not Promoted" - I don't want to have "stars" displayed for all rows in these scenarios.
How to set Default filter for Matrix or Table
Hi everyone, I want a matrix to show values for a particular filter, L1, from KPI Level by default. Note: I will use KPI Level as a slicer. When I click on the L2 slicer, the table should display L2 data. For L3 selection, it should show L3 data, and for L1, it should show L1 data. If no filters from the KPI Level slicer are selected, the table should display only L1 data by default. The table needs to include the following columns: - KPI Level - Region - Category - Target - Actual Below is the data view of the dataset: ChatGPT data prompt: https://chatgpt.com/share/b2a755d3-e928-4817-80ef-a643de3c6bf0 Dax formula i used: Defualt_Target = Var _a = SELECTEDVALUE(Data[KPI Level], "L1") Var _b = CALCULATE(sum(Data[Target]), FILTER(Data, Data[KPI Level]= _a )) RETURN _b I am not able to acheive the result what i wanted
Fill in a measure value to all rows in a new measure by group
Hi Members, I'm working on a dashboard where I need to compute the ratio between two measures. To be more specific, I'd like to get the ratio between Average Amount and Ref, both are measures, as shown in the screenshot. For each FY, the Ref value is the one in the WI category (indicated by arrows). I managed to get the WI category value show up in the Ref measure, but I am having difficulties to populate the value by FY group. My case is a bit complicated because I need to use slicers on FY/Size/Category to showcase the ratio in various scenarios. In the screenshot, Total Amount, Total Order, Average Amount are all measures. I did a little bit of search and came across three cases that are very close to my situation: Use a measure value of one row to populate all other rows New measure to take the value from 1 row and apply it to all rows in the table Divide Measure by its Max value However none of the solutions work in my case. It'd be appreciated if experts here could help me find a solutuon. Thank you for your time! ------------------------------------------- Sample PBIX file: filter_test.pbix Sample data: Sample.csv
create a measur with a filter ignoring filter on the page level
i have created a measure that calculates a oercentage with a filter. i have the same filter in my table visual but i want to ignore the filter that is in the visual and apply the filter in the measure. example: accessories achievement = calculate(achievement * .1 , category[type]= accessories, all(category[type)) my table visual should show the below category type core achievement accessories achievement gross aad core 94 8 renewal core 90 7 note: there's a filter on page level for the column type=core. i want to show accessories achievemnet with type= accessories ignoring this page level filter= core. currently with the measure i created above, accessories achievement is showing blank.
How to converting translate measure tableau Fixed to dax measure
I have a tableau measure that is : Fixed Cliente: Aderencia Ponderada = {Fixed [Cod Cliente Unif],MONTH([Data fat.1]),[Canal de Distribuição]:IIF(ISNULL([{Calc} Preço Bruto Ponderado (R$/Ton)]/[{Calc} Preço Alvo Ponderado (R$/Ton)]),NULL,[{Calc} Preço Bruto Ponderado (R$/Ton)]/[{Calc} Preço Alvo Ponderado (R$/Ton)],NULL)} I tried this measure in dax , but when I exploded this measure in other dimension in the same fact (fct_PA_VPP_Custo_CPV) - and this dimension is a column measure ("Grupo Repasse") and have circular dependency to this measure "Fixed Cliente" , this vision no have the same values to have in Tableau. VAR TabelaAgregada = SUMMARIZE( fct_PA_VPP_Custo_CPV, fct_PA_VPP_Custo_CPV[Cod Cliente Unif], fct_PA_VPP_Custo_CPV[mês], fct_PA_VPP_Custo_CPV[Canal de Distribuição], "PrecoBrutoPonderado", [Preço Alvo Perfomance_Preço Bruto (R$/Ton) ponderado], "PrecoAlvoPonderado", [Preço Alvo Perfomance_Preço Alvo Ponderado (R$/ton)], "Divisao ponderada", DIVIDE([Preço Alvo Perfomance_Preço Bruto (R$/Ton) ponderado], [Preço Alvo Perfomance_Preço Alvo Ponderado (R$/ton)], 0) ) RETURN SUMX( TabelaAgregada, [Divisao ponderada] ) Grupo Repasse = VAR ClienteAderencia = [Fixed Cliente: Aderencia Ponderada] VAR LimiteAderenciaEmpresarial = 0.8 VAR LimiteAderenciaRevenda = 0.85 RETURN IF( and(fct_PA_VPP_Custo_CPV[Canal de Distribuição] = "EMPRESARIAL" , ClienteAderencia< LimiteAderenciaEmpresarial ), "Baixa Aderência", IF ( and( fct_PA_VPP_Custo_CPV[Canal de Distribuição] = "REVENDA" ,ClienteAderencia < LimiteAderenciaRevenda), "Baixa Aderência", "Alta Aderência" ) ) column measure "Grupo repasse " in Power BI and Tableau . The measure in dax about fixed result the same total of in tableau, but when I exploded this measure in ohter dimension that not to be in sumarize , tha values were wrong : The vision in Tableau when I exploded the mesure Fixed with dimension "Grupo Repasse" But if I exploded in the same dimension that have in sumarize, the dax measure is the same in tableau and power bi how to resolved this in DAX ? I need converting these measure.Incorrect value if interaction with filter (Date Slicer) is on
here we can see the first table that has interaction with filters on top has wrong values but the second one when i turn the interactions off has right values. Here is my measure to calculate last year value ` Last_Year_value = CALCULATE( SUM(Profit_and_Loss[Value]), DATEADD('Calendar'[Date],-12,MONTH) )` I have a relation one sided between profit_and_loss table and Calendare table here it is the relation between the two tables I tried using "REMOVEFILTERS() / ALL() / ALLEXCEPT()", but non of them worked.How to incorporate the page level filters into a DAX Measure
Hello, I want to calculate the number of days on the basis of the Dates provided in the page filter using a DAX measure. For e.g if the 'Start Date' is on or after 1-Nov-2020 & 'End Date' is on or before 17-Dec-2020, we should get the output as 47 i.e. no.of days between start & end date. Please find the below snapshot for better understanding. I tried using below measure but its not working. NoOfDaysBetween=DATEDIFF(SELECTEDVALUE('TimeZone'[Begin]),SELECTEDVALUE('TimeZone'[End]) Thanks!
