Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello - I am looking for some help.
I have the following three tables in the model:
1. Calendar table - has all the dates
2. DailyTxdataSQL - Fact table recording sales by day and country.
3. Region - has a mapping of the country, display country (group of smaller countries), etc.
I am trying to create a table where I would like to k now which country has how many days in a week/month/year, etc.
The DAX formula below works perfectly when there is a daily sale record. But it doesn't work when a country doesn't have a sales record (due to a system or data issue). For Example, if there were no sales on 01-01-2023 and it was a working day, the row is not created. I would like to create a row and count it in the working day. (Note: DailyTxdataSQL tables have a column [Weekdays] which is flagged as 0 and 1) 0 means holiday or weekend, and 1 means working day.
Thank you in advance.. regards,
Hi @abhishekrws01 ,
Based on the information you provided, you can try to solve the problem with the dax below:
Working Days Count =
CALCULATE(
COUNTROWS(Calendar),
Calendar[Date] >= MIN(DailyTxdataSQL[Date]),
Calendar[Date] <= MAX(DailyTxdataSQL[Date]),
Calendar[Weekdays] = 1,
NOT(ISBLANK(DailyTxdataSQL[Country])),
ALLEXCEPT(DailyTxdataSQL, DailyTxdataSQL[Country])
)
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try the following :
Working Day_Table =
SUMMARIZE(
ADDCOLUMNS(
CROSSJOIN(
'Calendar',
Region
),
"DailyTxData", RELATED(DailyTxDataSQL[Weekdays])
),
'Calendar'[Date],
Region[Country],
Region[Display Country Name],
"Working Days", IF(ISBLANK(MAX([DailyTxData])), 0, MAX([DailyTxData]))
)
Hi - Thank you for looking into it. I get the following error.
Thanks
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |