how to replicate a specific tableau measure in power bi using DAX
I have a critical problem to replicate a tableau measure in power bi using DAX. this is the tableau measure: ``` tableau_measure = [working_time] / CASE ([Country]) WHEN 'Italy' THEN 40 * 60 * ({FIXED [Country] : COUNTD(IIF([Flag Working Day Italy]=1,[Calendar Date],NULL))}) WHEN 'Germany' THEN 50 * 60 * ({FIXED [Country] : COUNTD(IIF([Flag Working Day Germany]=1,[Calendar Date],NULL))}) WHEN 'Austria' THEN 40 * 60 * ({FIXED [Country] : COUNTD(IIF([Flag Working Day Austria]=1,[Calendar Date],NULL))}) WHEN 'Poland' THEN 50 * 60 * ({FIXED [Country] : COUNTD(IIF([Flag Working Day Poland]=1,[Calendar Date],NULL))}) WHEN 'Romania' THEN 60 * 60 * ({FIXED [Country] : COUNTD(IIF([Flag Working Day Romania]=1,[Calendar Date],NULL))}) ELSE 315 * ({FIXED [Country] : COUNTD(IIF([Flag Working Day Generic]=1,[Calendar Date],NULL))}) END ``` My power BI data model is: - table_A (fact table) -> contains information about some working activities with relating column working_time (in seconds) and column date_ref (dd-mm-yyyy), and others columns not relevant.. - table_B (dimension table) -> contains column [Country] (as string), and others columns not relevant.. - table_C (dimension table, is a calendar table) -> contains columns such as [Flag Working Day Italy], [Flag Working Day Germany], [Flag Working Day Austria], [Flag Working Day Poland], [Flag Working Day Romania], [Flag Working Day Generic] (these columns contains two different values, e.g. values 1 for working days and values 0 otherwise ) and others columns not relevant.. table_B is related on table_A by one to many cardinality table_C is related on table_A by one to many cardinality I tryied to replicate that measure in DAX as below: power_bi_measure = var num = table_A[working_time] var den = SWITCH ( RELATED ( table_B[Country] ), "Italy", 340 * 60 * CALCULATE ( DISTINCTCOUNT ( table_A[date_ref] ), ALL ( table_A), FILTER ( table_C , table_C [flag_working_day_italy] = 1 ) ), "Germany", 350 * 60 * CALCULATE ( DISTINCTCOUNT ( table_A[date_ref]), ALL ( table_A), FILTER ( table_C , table_C [flag_working_day_germany] = 1 ) ), "Austria", 340 * 60 * CALCULATE ( DISTINCTCOUNT ( table_A[date_ref]), ALL ( table_A), FILTER ( table_C , table_C [flag_working_day_austria] = 1 ) ), "Poland", 350 * 60 * CALCULATE ( DISTINCTCOUNT ( table_A[date_ref] ), ALL ( table_A), FILTER ( table_C , table_C [flag_working_day_poland] = 1 ) ), "Romania", 360 * 60 * CALCULATE ( DISTINCTCOUNT (table_A[date_ref] ), ALL ( table_A), FILTER ( table_C , table_C [flag_working_day_romania] = 1 ) ), 315 * CALCULATE ( DISTINCTCOUNT ( table_A[date_ref] ), ALL ( table_A ), FILTER ( table_C , table_C [flag_working_day_generic] = 1 ) ) ) return DIVIDE ( num, den, 0 ) On my display page, I have added a slicer that contains the date_ref column, set to the "between" mode, for example, from 10-1-2023 to 11-1-2023. I am encountering an error in the calculation of working_days because it does not take into account the calendar days (in table_C) within the range of dates filtered in the slicer, specifically the days from 10-1-2023 to 11-1-2023. Instead, it considers only the days in table_A (from 10-1-2023 to 11-1-2023) for which there are working activities. For example, considering the above filters, the working days for the country Germany are 22. (undesired output): In this case, flag_working_day_germany = 9 is due to the fact that the activities of process_working_activity = BB are present only on 9 calendar days in table_A. (desired output): Our desired output should consider all values of flag_working_day_germany present in the column, regardless of other activities, and only refer to the filter 10-1-2023, 11-1-2023, resulting in 22 days.
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.
Power BI Vs Tableau
Hi everyone, When I am looking for a new job, I noticed that Tableau is more common than Power BI. But I beleive Power BI has more powerful tools for data analysis. Can someone help which one is better? And what points Power BI has more powerful than Tableau? Thank you in advance
