Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
simon_pbi_92
Regular Visitor

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):
image_1.PNG

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):
image_2.PNG

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.

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

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.

 

 

 

 

View solution in original post

2 REPLIES 2
v-rongtiep-msft
Community Support
Community Support

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.

 

 

 

 

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 🙂



Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.