Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a Date table like this
Date | Year-Month | Sweden | Denmark | Norway |
2020-01-01 | 2020-01 | 0 | 1 | 0 |
2020-01-02 | 2020-01 | 0 | 1 | 0 |
2020-01-03 | 2020-01 | 1 | 1 | 0 |
2020-01-04 | 2020-01 | 1 | 1 | 1 |
2020-01-05 | 2020-01 | 0 | 0 | 1 |
2020-01-06 | 2020-01 | 0 | 0 | 1 |
2020-01-07 | 2020-01 | 1 | 1 | 1 |
2020-01-08 | 2020-01 | 1 | 1 | 0 |
2020-01-09 | 2020-01 | 1 | 1 | 1 |
2020-01-10 | 2020-01 | 1 | 1 | 0 |
Then I have the Company table like this
Company | Country |
Swedish Company | Sweden |
Danish Company | Denmark |
Norwegian Company | Norway |
Would like to calculate Sales per working day for each company based on the total working day from the calendar. So I did the following measures:
Working day = SWITCH(TRUE(),
SELECTEDVALUE(dimCompany[Country])="Sweden",SUM(dimDate[Sweden]),
SELECTEDVALUE(dimCompany[Country])="Norway",SUM(dimDate[Norway]),
SELECTEDVALUE(dimCompany[Country])="Denmark",SUM(dimDate[Denmark]),
0)
Sales per working day = DIVIDE([Sales],[Working day],0)
It works well if I just choose one country... of course. So if I would like to choose more than one country, how should I write my DAX?
Example: If I pick Swedish and Danish Company, I want to see the result of: (SUM(dimDate[Sweden])+SUM(dimDate[Denmark]))/2
If I pick 3 companies, I want to see the result of (SUM(dimDate[Sweden])+SUM(dimDate[Denmark])+SUM(dimDate[Norway]))/3
Thank you very much.
@Anonymous Well, you could use the IN operator like
"Sweden" IN DISTINCT('Company'[Country]) && "Norway" IN DISTINCT('Company'[Country])
That said, it would be far better to unpivot the country columns in your first table and create a relationship between your two tables and then there is zero need for fancy DAX.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |