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

[simon_pbi_92]

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.

[v-rongtiep-msft · Accepted solution]

Hi @simon_pbi_92 ,

Please try to use the following measure.

power_bi_measure = 
VAR num = SUM ( table_A[working_time] )
VAR den = 
    SWITCH (
        TRUE (),
        MAX ( table_B[Country] ) = "Italy", 40 * 60 * COUNTROWS ( FILTER ( table_C, table_C[Flag Working Day Italy] = 1 ) ),
        MAX ( table_B[Country] ) = "Germany", 50 * 60 * COUNTROWS ( FILTER ( table_C, table_C[Flag Working Day Germany] = 1 ) ),
        MAX ( table_B[Country] ) = "Austria", 40 * 60 * COUNTROWS ( FILTER ( table_C, table_C[Flag Working Day Austria] = 1 ) ),
        MAX ( table_B[Country] ) = "Poland", 50 * 60 * COUNTROWS ( FILTER ( table_C, table_C[Flag Working Day Poland] = 1 ) ),
        MAX ( table_B[Country] ) = "Romania", 60 * 60 * COUNTROWS ( FILTER ( table_C, table_C[Flag Working Day Romania] = 1 ) ),
        315 * COUNTROWS ( FILTER ( table_C, table_C[Flag Working Day Generic] = 1 ) )
    )
RETURN
    DIVIDE ( num, den )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[simon_pbi_92]

Thank you for the response. Unfortunately, the issue persists.
Let's simplify the measure in this way, considering only the part that seems to be problematic (related to the calculation of working days), namely:

countrows_working_day_germany = COUNTROWS ( FILTER ( table_C, tableC[Flag Working Day Germany] = 1 ) )

Unfortunately, your measure and mine depend on the dates of the tracked working activities (present in table_A). For example, when I consider a time period, from Oct-1-2023, to Nov-1-2023 (entered in my Power BI page as a slicer), I expect to have 22 working days (in the date field of table_C).
However, what happens is the following situation:

| process_working_activity | countrows_working_day_germany |
|---------------------------|-------------------------------|
| AA                        | 22                            |
| BB                        | 2                             |
| CC                        | 11                            |

Include a description of the situation with values 22, 2, and 11, which depend on the tracked activities on process_working_activity for 22, 2, and 11 distinct days.
So, the problem is that the measure should calculate the working days based on the date field in table_C without considering the tracked activities. Having this output:

| process_working_activity | countrows_working_day_germany |
|---------------------------|-------------------------------|
| AA                        | 22                            |
| BB                        | 22                            |
| CC                        | 22                            |

Do you have any ideas how to perform this (independent) calculation? Thank you in advance 🙂