Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good Morning All,
I have column called MIS (month in Service) . It can have different values 0,2,3,4 etc. I have another custom column that calculate the maximum MIS that particular record should have. All I want to do is that start the MIS from 0 to max MIS and I am not sure how to do.
The column "Number" is the maximum number that MIS should go to. "Count without zero" is simple count. And running total is just that. The idea bahind is that MIS should go to 18 (Number) and just repeating the running total.
Any help will be greatly appreciated.
so basically I want output to look like following
201801 18 0 1 1
201801 18 1 0 1
201801 18 2 0 1
201801 18 3 1 2
201801 18 4 1 3
201801 18 5 0 3
201801 18 6 2 5
201801 18 7 1 6
201801 18 8 0 6
All the way to
201801 18 18 0 6
Hopefully that will help understand what I am trying to do.
Solved! Go to Solution.
Hi mukhan
Based on your description, did you want to get result like below?
If so, you could create a new table use below measure below(ALLMIS)
ALLMIS= VAR newt = GENERATESERIES ( 0, 18 ) RETURN SUMMARIZE ( newt, [Value], "Monnthyear", "201801", "number", 18 )
create relationship based on MIS
Measure 3 = VAR selected = SELECTEDVALUE ( MIS[MIS] ) RETURN IF ( selected IN VALUES ( AllMIS[MIS] ), COUNT ( MIS[MIS] ), 0 )
runningtotal = SUMX ( FILTER ( ALL ( AllMIS ), AllMIS[MIS] <= MIN ( AllMIS[MIS] ) ), [Measure 3] )
If your data sample is not like this or this is not what you want, please inform me your detailed sample data and your expecting output.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi mukhan
Based on your description, did you want to get result like below?
If so, you could create a new table use below measure below(ALLMIS)
ALLMIS= VAR newt = GENERATESERIES ( 0, 18 ) RETURN SUMMARIZE ( newt, [Value], "Monnthyear", "201801", "number", 18 )
create relationship based on MIS
Measure 3 = VAR selected = SELECTEDVALUE ( MIS[MIS] ) RETURN IF ( selected IN VALUES ( AllMIS[MIS] ), COUNT ( MIS[MIS] ), 0 )
runningtotal = SUMX ( FILTER ( ALL ( AllMIS ), AllMIS[MIS] <= MIN ( AllMIS[MIS] ) ), [Measure 3] )
If your data sample is not like this or this is not what you want, please inform me your detailed sample data and your expecting output.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |