Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have the following data:
Team Item DaysBackFromToday
A 123 90
A 456 3
A 789 0
A 412 120
A 892 150
A 105 365
B 501 91
B 502 92
What I need is a field in Dax to show per Team the following
Team A: 0-90 days: 2 90+: 3 %0-90 days: 40% (2/5 is the calc) %90+: 60%
Team B: 0-90 days: 0 90+: 2 %0-90 days: 0% %90+: 100%
Thanks
Solved! Go to Solution.
I was able to figure this out.
I needed to use the DIVIDE with alternate result instead of using an If condition, so I had to use the DAX DIVIDE.
Hi @EaglesTony ,
I'll start by showing you the test data I use:
You can use the following DAX to create a measure:
Calculate the days of A on 0-90:
COUNT_A_0-90 =
CALCULATE(
COUNT('Table'[DaysBackFromToday]),
FILTER(
'Table',
'Table'[Team] = "A" &&
'Table'[DaysBackFromToday] > 0 &&
'Table'[DaysBackFromToday] <= 90
)
)
Calculate the days of A on 90+:
COUNT_A_90+ =
CALCULATE(
COUNT('Table'[DaysBackFromToday]),
FILTER(
'Table',
'Table'[Team] = "A" &&
'Table'[DaysBackFromToday] > 90
)
)
Calculate the total number of A's, excluding cases where days=0:
Total_A_>0 =
CALCULATE(
COUNT('Table'[DaysBackFromToday]),
FILTER(
'Table',
'Table'[Team] = "A" &&
'Table'[DaysBackFromToday] > 0
)
Calculate the proportion of days in A on 0-90:
Percentage_A_0-90 =
[COUNT_A_0-90] / [Total_A_>0]
Calculate the proportion of days in A on 90+:
Percentage_A_90+ =
[COUNT_A_90+] / [Total_A_>0]
And the final output is shown in the following figure:
For B, you just need to change the A in each measure to B, and I won't repeat it for you.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, but I don't think this will work, as I have a variable amount of teams at any one point, meaning I could have 2 teams or 20 teams when I do the refresh.
Hey @EaglesTony
Use these measures:
0-90 days =
Var _A = FILTER('Table','Table'[Days Back From Today]<90)
Return
COUNTROWS(_A)+0
90+ days =
Var _A = FILTER('Table','Table'[Days Back From Today]>90)
Return
COUNTROWS(_A)+0
% 0-90 days =
Var _A = FILTER('Table','Table'[Days Back From Today]<90)
Var _B = COUNTROWS(_A)+0
Var _C = [0-90 days]+[90+ days]
Return
DIVIDE(_B,_C)
%90 days =
Var _A = FILTER('Table','Table'[Days Back From Today]>90)
Var _B = COUNTROWS(_A)+0
Var _C = [0-90 days]+[90+ days]
Return
DIVIDE(_B,_C)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
I got the wrong requirements, it shouldn't be a count but a total based on a column called points.
So for the following:
Team Item DaysBackFromToday Points
A 123 90 3
A 456 3 2
A 789 0 5
A 412 120 1
A 892 150 5
A 105 365 5
B 501 91 8
B 502 92 2
It should be:
Team A: 0-90 days: 10 (pts) 90+: 11 (pts) %0-90 days: 48% (10/21 is the calc) %90+: 52%
Team B: 0-90 days: 0 (pts) 90+: 10 (pts) %0-90 days: 0% %90+: 100%
I think I might need to create these in M instead of DAX ?
Maybe have a "Y" or "N" column for LessThan90 and Over90 and use that somehow in the table on the UI ?
Hey @EaglesTony
Ok, just updated those measures as below:
90+ days =
Var _A = FILTER('Table','Table'[DaysBackFromToday]>90)
Return
SUMX(_A,[Points])+0
0-90 days =
Var _A = FILTER('Table','Table'[DaysBackFromToday]<=90)
Return
SUMX(_A,[Points])+0
%90 days =
Var _B = [90+ days]
Var _C = [0-90 days]+[90+ days]
Return
DIVIDE(_B,_C)
% 0-90 days =
Var _B = [0-90 days]
Var _C = [0-90 days]+[90+ days]
Return
DIVIDE(_B,_C)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thanks, the strange thing is that when I put this field in the table in the report, the teams are showing the correct information, but there is a row that is blank (FYI: I have a slicer using Team name):
Hey @EaglesTony
Remove that +0 from 90+ days and 0-90 days measures, that will be fixed
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
What I did was in the Filters don't show blank teams.
Now it is working incorrectly when trying to use my slicer selecting team is is showing all teams still:
Before selecting Team:
After selecting Team A:
Hi @EaglesTony
Have you solved your problem?
Can you please provide the pbix file if it hasn't been resolved? It's hard to tell what the problem is just from your description.
Best Regards,
Dino Tao
Hi,
No my problem still persists when I add the slicer, not sure why, as it started after I added these fields to the report.
One thing I did find out is that when I have put the whole number fields on the report, it doesn't impact the slicer, however when I put in a percetange value on the report, it causes the issue of showing all the teams, regardless of the slicer value.
I can't send over the .pbix due to company policy.
Hi @EaglesTony ,
Please try this DAX to create a new column:
Group =
IF (
'Table'[DaysBackFromToday] >= 0
&& 'Table'[DaysBackFromToday] <= 90,
"0-90 days",
"90+ days"
)
The results are as follows:
If you are still having problems, please point out the problem in the pbix file I provided.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I have the columns created correctly, the issue is with the slicer, when I select a single team and if the report only has whole numbers (i.e. DaysBackFromToday), it works in only showing that team, however when I select a single team and the report has percentages (i.e. PctWithin90Days), it shows the single teams values AND all the other teams data as blank, but their percentage as 0%)
Hi @EaglesTony ,
I know your issue is currently on slicer, and I'm concerned that it may be caused by the DAX you were using earlier.
Since I don't see the situation you're experiencing with the DAX I'm using myself, I hope you can try a different DAX and see if the slicer is still having problems.
If the problem persists, please continue to follow the thread.
Best Regards,
Dino Tao
I would think I need a new DAX for the percentage calculation as well ?
Hi @EaglesTony ,
Have you tried a new measure for you situation and has the problem been solved?
If it doesn't solve the problem, I suggest you to use the pbix that I provided earlier and test your need, the problem you are facing didn't occur in my test situation.
Best Regards,
Dino Tao
I assume I would use a DAX query to build the percentage column as well ?
Hi @EaglesTony ,
No need to create a separate measure to get the percentage.
Please follow the steps below:
Create a calculated column for grouping (0-90 and 90+), and the DAX I have provided before;
Place the point column in the Value column and use the aggregation function and the display format (SUM, show column total) to display the summary value as well as the percentage.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I used your DAX query for Group and I put this field "Group" on my table visual, however it is giving me 2 rows now for each team I select (1 for 90+ days and 1 for 0-90 days) and I don't see any percentage.
The issue seems to be tied to a percentage column using in conjuntion with the Team slicer.
I was able to figure this out.
I needed to use the DIVIDE with alternate result instead of using an If condition, so I had to use the DAX DIVIDE.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
19 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
22 | |
22 |