Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Community,
so I have data of persons who have achieved trips over time like this:
Date | Name | Trip
xx1 | Max | 1
xx1 | Jerry | 1
xx2 | Max | 1
xx2 | Dupe | 1
xx3 | Max | 1
xx4 | Dupe | 1
and I have a measure Total Trips = sum( [Trip] ) which sums up the Trips column
I want to be able to group by the Total Trips achieved at every point in time (filtered on date).
I mean something like this:
For Filtered Date : xx1 - xx3
Name | 1 Trip | 2 Trips | 3 Trips | >3 Trips | Total
Max | 0 | 0 | 3 | 0 | 3
Jerry | 1 | 0 | 0 | 0 | 1
Dupe | 1 | 0 | 0 | 0 | 1
Total | 2 | 0 | 3 | 0 | 5
where
* 1 Trip := Total Trips = 1
* 2 Trips := Total Trips = 2
* 3 Trips := Total Trips = 3
* >3 Trips := Total Trips > 3
Please help me.
Thanks.
[E]
Solved! Go to Solution.
Hi @Emmaxson,
Please use the formulas below
1 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=1,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=1),IF(Test[Total Trips]=1,Test[Total Trips])))+0 2 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=2,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=2),IF(Test[Total Trips]=2,Test[Total Trips])))+0 3 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=3,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=3),IF(Test[Total Trips]=3,Test[Total Trips])))+0 >3 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]>3,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]>3),IF(Test[Total Trips]>3,Test[Total Trips])))+0
You will get right result.
ANGELIA
Hi @Emmaxson,
Please create four measures using the formulas below.
1 Trip = IF(Test[Total Trips]=1,1,0) 2 Trip = IF(Test[Total Trips]=2,2,0) 3 Trip = IF(Test[Total Trips]=3,3,0) >3 Trip = IF(Test[Total Trips]>3,Test[Total Trips],0)
Then create a slicer including the date, create a matrix visual, select the [name] as row, the measures as values fields. You will get the expected result.
Please download the attachment to test and review.
Best Regards,
Angelia
Thanks for this @v-huizhn-msft BUT the total row is not working fine. Please check. For each measure, it gives a total of 0 aside from the last measure; how can we work around this? Because in the end, I want to know how many people fall under each category.
Also, have a look at this: http://www.daxpatterns.com/dynamic-segmentation/
I tried working with Dynamic Segmentation but was not successful in my exploits.
Please help.
Thanks
Hi @Emmaxson,
Please use the formulas below
1 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=1,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=1),IF(Test[Total Trips]=1,Test[Total Trips])))+0 2 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=2,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=2),IF(Test[Total Trips]=2,Test[Total Trips])))+0 3 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=3,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=3),IF(Test[Total Trips]=3,Test[Total Trips])))+0 >3 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]>3,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]>3),IF(Test[Total Trips]>3,Test[Total Trips])))+0
You will get right result.
ANGELIA
@v-huizhn-msft This is good. Does this job. #ThumbsUp
However can you help me understand it so I can reuse it where and when necessary in other ways.
For instance, I want to check for each 1 Trip, 2 Trips, etc.; I want to know the count of drivers that fall into each category at every point in time.
Looking forward to your favourable response.
Thanks once again.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |