how to replicate a specific tableau measure in power bi using DAX

Question

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 power bi page, I have added a slicer that contains the date_ref column, set to the "between" mode. For example, from 2023-10-1 to 2023-11-1.

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 2023-10-1 to 2023-11-1. Instead, it considers only the days in table_A (from 2023-10-1 to 2023-11-1) 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 ouput):

my 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 from 2023-10-1 to 2023-11-1, resulting in 22 days.

Answer 1

Having ALL(table_A) essentially removes all filters/context/relationships.

Try:

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]), table_C[flag_working_day_italy] = 1 ),
    "Germany", 350 * 60 * CALCULATE( DISTINCTCOUNT(table_A[date_ref]), table_C[flag_working_day_germany] = 1 ),
    "Austria", 340 * 60 * CALCULATE( DISTINCTCOUNT(table_A[date_ref]), table_C[flag_working_day_austria] = 1 ),
    "Poland", 350 * 60 *  CALCULATE( DISTINCTCOUNT(table_A[date_ref]), table_C[flag_working_day_poland] = 1 ),
    "Romania", 360 * 60 * CALCULATE( DISTINCTCOUNT(table_A[date_ref]), table_C[flag_working_day_romania] = 1 ),
    315 * CALCULATE( DISTINCTCOUNT(table_A[date_ref]), table_C[flag_working_day_generic] = 1 )
  )
return DIVIDE ( num, den, 0 )