Count number of public holiday by employees located on different countries

[yanx1990]

Dear community, 

I searched on several posts and on ChatGPT but I can't find a good solution for what I exactly need. 

My model is the following : 

The Table_Date content is easy to guess (it's a standard Date table)
The Dim_Employee is for example : 

ID	Name	CountryCode
1	John Doe	FR
2	Alice Keys	NL
3	Mike Scott	FR

On the Public Holiday table, I have these data : 

Date	Country	HolidayName
2024/04/01	FR	Easter Day
2024/04/01	NL	Easter Day
2024/04/27	NL	Netherland National Day

I would like a measure that will sum the number of public holiday by employees. 
In the report, I would have something like this : 

Is it something easy to do ? 
I tried several DAX measure but it always fails somewhere to give me the good results. 

NB : If i change the CountryCode on the top left slicer, it should also filter the measure. So if I choose NL, it would display 2 sum of public holidays. If I choose FR, it would display 2 sum of public holidays too.

I hope it is clear and you will be able to help me 🙂 !

Kind regads,

Yannick

[Rajesh_Vodela]

I tried like this and it worked for me as per your question

let me know its is correct or not.
-- Measure to calculate the count of holidays

HolidayCnt = COUNT(Holiday_Table[HolidayName])
-- Final Measure

Holiday Count =

SUMX(

    ADDCOLUMNS(

    SUMMARIZECOLUMNS(

        Dim_Emp[Name],

        Dim_Emp[CountryCode]

    ),

    "MyCount", [HolidayCnt]

    ),

    [MyCount]

)

[v-yangliu-msft · Accepted solution]

Hi  @yanx1990 ,

Your solution is great, @tamerj1 . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference. 

Here are the steps you can follow：

1. Create measure.

Measure =
var _mindate=MINX(ALLSELECTED('Table'),[Date])
var _maxdate=MAXX(ALLSELECTED('Table'),[Date])
var _count=
COUNTX(
    FILTER('Public Holiday table',
    'Public Holiday table'[Date]>=_mindate&&'Public Holiday table'[Date]<=_maxdate
    &&'Public Holiday table'[Country]=MAX('Dim_Employee'[CountryCode])),[HolidayName])
return
_count

Measure 2 =
SUMX(VALUES('Dim_Employee'[Name]),[Measure])

2. Result:

Best Regards,

Liu Yang

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

[tamerj1]

Hi @yanx1990 

please try

Public Holidays =
SUMX (
    Dim_Emplyee,
    CALCULATE (
        COUNTROWS ( Public_Holidays ),
        USERELATIONSHIP ( Public_Holidays[Country], Dim_Employee[CountryCode] )
    )
)

[yanx1990]

Hi, 

Thanks a lot for your solution. It seems to be working but when I have more data it crash with a "The exception was raised by the IDataReader interface". I think this is due to my relationship between Public Holidays and Dim_Employees. I will have to review that model.

@v-yangliu-msft : Your solution seems great because it does not use the relationship so there's no crash. However, when I select both region, the sum of Public Holidays seems incorrect. But I think this is related to the MAX('Dim_Employees'[CountryCode]). I will dig this solution because I'm quite sure it's the way I'll got the good value for both the Table and the Card 🙂

If you have any idea, I'm open too 🙂

[yanx1990]

Hi again @v-yangliu-msft ,
Sorry you were completely right. I did it in one measure but indeed, 2 measures are needed in that case.

Thank you very much. I marked it as solution 🙂 !