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.
Hi,
I need some help to write a DAX formula, I've tried so many already that I get lost now 😞
Here is what I need as a final result, correct numbers are in green, wrong ones are the red (what I have for the moment) :
Date | Destination | Number of available trains | Limit per day | percentage (number of available trains/limit per day) |
18/08/2021 | BELVAL | 3 | 10 | 0.3 |
19/08/2021 | BELVAL | 2 | 10 | 0.2 |
18/08/2021 | MERTERT | 3 | 50 | 0.06 |
19/08/2021 | MERTERT | 4 | 50 | 0.08 |
Total | 12 |
Global percentage : 0.1
My problem is to retrieve the correct Total of limit per day. Instead of having 120, I have 100 because I put a max(limit per day) to get the limit for each row. Here are how my data look like in the table :
date | train | destination | limit
18/08/2021 | T1 | BELVAL | 10
18/08/2021 | T2 | BELVAL | 10
18/08/2021 | T3 | BELVAL | 10
19/08/2021 | T1 | BELVAL | 10
19/08/2021 | T2 | BELVAL | 10
18/08/2021 | T4 | MERTERT | 50
18/08/2021 | T5 | MERTERT | 50
18/08/2021 | T8 | MERTERT | 50
19/08/2021 | T6 | MERTERT | 50
19/08/2021 | T4 | MERTERT | 50
19/08/2021 | T5 | MERTERT | 50
19/08/2021 | T7 | MERTERT | 50
Can you please help me to get the green result instead of the red one ? Huge thanks for your help!!
Solved! Go to Solution.
Hi amitchandak,
Thanks for your answer but the 2 formulas don't bring the correct results 😕 It's weird you don't have the destination field in your formulas isn't it ?
However, I finally managed to write a formula that seems OK, can you please give me your feeling on it ?
M_percentagelimit =
VAR nbdays = countrows('DimDate')
VAR pct = sumx(
summarize(Disponibilite,Disponibilite[destination],"_1",max(Disponibilite[limit]))
,[_1])
* nbdays
RETURN nb
Thanks, AnSo
@Anonymous , Use these two measures for the last 2
sumx(summarize(Table,[date], [train],[limit]), [Limit])
sumx(summarize(Table,[date], [train],"_1", divide(countrows(Table),max(Table[Limit]))), [_1])
Hi amitchandak,
Thanks for your answer but the 2 formulas don't bring the correct results 😕 It's weird you don't have the destination field in your formulas isn't it ?
However, I finally managed to write a formula that seems OK, can you please give me your feeling on it ?
M_percentagelimit =
VAR nbdays = countrows('DimDate')
VAR pct = sumx(
summarize(Disponibilite,Disponibilite[destination],"_1",max(Disponibilite[limit]))
,[_1])
* nbdays
RETURN nb
Thanks, AnSo
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |