Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi,
Use case: Count the number of cars available per month based on a start and end dates.
Model: The model is simple one calendar and another fact table without relationship.
Dax Formulas: I have achive this with 2 formulas, one is correct but with bad performance another total is not correct for totals but the performance is good:
Pbix File: File with Example
Could you support me to achive the correct value with good performance.
Solved! Go to Solution.
You can try this formula:
_Cars_PerMonthAndDay =
Var __Result = SUMX(
'Calendar',
Var __Date = 'Calendar'[Date]
return COUNTROWS(
FILTER(
Cars,
Cars[ValidFrom] <= __Date &&
Cars[ValidUntil] >= __Date
)
)
)
RETURN __Result
In my observation, the results are accurate and it is performing better than your version.
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
|
_Cars_PerMonthAndDay =
SUMX(
Cars,
VAR __from = Cars[ValidFrom]
VAR __until = Cars[ValidUntil]
RETURN
CALCULATE(
COUNTROWS( 'Calendar' ),
'Calendar'[Date] >= __from,
'Calendar'[Date] <= __until
)
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |
_Cars_PerMonthAndDay =
SUMX(
Cars,
VAR __from = Cars[ValidFrom]
VAR __until = Cars[ValidUntil]
RETURN
CALCULATE(
COUNTROWS( 'Calendar' ),
'Calendar'[Date] >= __from,
'Calendar'[Date] <= __until
)
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |
You can try this formula:
_Cars_PerMonthAndDay =
Var __Result = SUMX(
'Calendar',
Var __Date = 'Calendar'[Date]
return COUNTROWS(
FILTER(
Cars,
Cars[ValidFrom] <= __Date &&
Cars[ValidUntil] >= __Date
)
)
)
RETURN __Result
In my observation, the results are accurate and it is performing better than your version.
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
|
User | Count |
---|---|
17 | |
16 | |
14 | |
13 | |
12 |
User | Count |
---|---|
17 | |
14 | |
14 | |
10 | |
8 |