Problem with measures aggregated on different dimensions

[Anonymous]

Hi everyone,

I have a problem with a metric calculated based on a date dimension. My requirement is that the metric calculate the number of people in the organization.

With two conditions:

1) If we do not include the date of the fact in the visualization (fact_date), the metric has to add the number of people in the last period of the date of fact (fact_date).

2) If we include the date of the fact (fact_date) in the visualization, the metric has to return the result for the fact date that exists.

The metric is working fine with some atributes but not with one addicional date atribute (end_contract_date).

The definition of the metric is:

Total_Person = IF(HASONEFILTER('RRHH'[fact_date])=TRUE()||HASONEVALUE('RRHH'[fact_date])=TRUE();

SUM('RRHH'[num_tot_per])

;

CALCULATE(SUM('RRHH'[num_tot_per]); LASTNONBLANK('RRHH'[fact_date];SUM('RRHH'[num_tot_per]))

))

It works fine, if no attributes or dimensions are used, the metric gets the number of people from the last date of fact_date:

Total Person (Last fact_date)

it corresponds to the code: 

CALCULATE(SUM('RRHH'[num_tot_per]); LASTNONBLANK('RRHH'[fact_date];SUM('RRHH'[num_tot_per]))

)

If we add the atribute atrib1 (from RRHH table), it works fine, gets the total_person group by atrib1 for the last date of fac_date date:

Total_Person by atrib1 (Last fact_date values)

If we add fact_date it works fine, the metric obtains the value of Total_Person for all the fact_date (not the last), the code below works without problem:

IF(HASONEFILTER('RRHH'[fact_date])=TRUE()||HASONEVALUE('RRHH'[fact_date])=TRUE();

SUM('RRHH'[num_tot_per])   

Total Person by atrib1 and fact_date (not last fact_date)

The problem comes when entering another field, in this case the end of contract date (end_contract_date), which I have converted to string on source database to avoid other problems.

In this case, the metric doesn't work and when the fact_date date is not used, it gets values ​​previous to the last fact_date date.

Values out of date_fact from Total_Person metric

This behavior is not valid. The metric should exclude data for end_contrac_date  values '19991231' that corresponds to a date before (2019-11-30),  only should gets data for the last date of fact_date (2019-12-31).

Could someone help me to solve the error? I have used this type of metric on various tables and they work correctly except from this metric. The HHRR table is not related to any table and is not a model problem.

Thank you and Best Regards,

Iván.

[Anonymous]

I don't exactly yet understand the issue... but I can tell you that your model is not a good one. Please watch these to see how to create good, reliable and correct models:

https://youtu.be/78d6mwR8GtA

https://youtu.be/_quTwyvDfG0

Bear in mind that if you don't follow the Best Practices you'll be having bugs in your calculations without even realizing this. This is not a joke. Please read this to see what issues you can face:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Also, please note that each and every sound, good model needs to have a DATE TABLE and slicing by columns of a fact table is strictly forbidden. Slicing can be only carried out through dimensions. You should have all columns in your fact tables HIDDEN.

Best

D