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.
