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.
Hello,
From a source table like:
Date | Id | Hours |
01/01/2020 | A | 10 |
02/01/2020 | A | 8 |
03/01/2020 | A | 9 |
04/01/2020 | B | 7 |
05/01/2020 | B | 10 |
02/02/2020 | A | 11 |
03/02/2020 | A | 4 |
04/02/2020 | B | 3 |
05/02/2020 | C | 6 |
15/03/2020 | A | 8 |
16/03/2020 | B | 3 |
17/03/2020 | C | 2 |
18/03/2020 | C | 15 |
I'm trying to get a table with the count of Ids per Month and Id, clasified by range of values, so I'd end up with this:
Month | Ids whose monthly Sum Hours < 10 | Ids whose monthly Sum Hours >= 10, <20 | Ids whose monthly Sum Hours >20 |
Jan | 1 | 1 | |
Feb | 2 | 1 | |
Mar | 2 | 1 |
I'm trying to figure out the measure(s) to get this, could you guys help, please?
Thanks
Solved! Go to Solution.
Hi,
I copied your Data. My table name is Feuil 7
Here is the 3 measures :
// You should have a Calendar table in your model
// with all the chunks of time that you desire.
// This is BEST PRACTICE and please stick to it
// if you don't want to have problems along the way.
// Once you've got this, you can write a measure...
// First, create a disconnected table in the model that'll
// store the categories, like so:
[Categories of IDs] = // calc table
SELECTCOLUMNS(
{
("Sum Of Hours in [0, 10)", 0, 10),
("Sum Of Hours in [10, 20)", 10, 20),
("Sum of Hours in [20, ∞)", 20, 10000000000)
},
// Exposed column for categeries of the ID's
"Category", [Value1],
// Technical hidden column
"SumLowerBoundInclusive", [Value2],
// Technical hidden column
"SumUpperBoundExclusive", [Value3]
)
// Then the measure... which will work for
// any piece of time, not only months.
[# IDs] =
SUMX(
CROSSJOIN(
'Categories of IDs',
ADDCOLUMNS(
VALUES( YourTable[Id] ),
"@SumOfHours",
CALCULATE( SUM( YourTable[Hours] ) )
)
),
var Hours = [@SumOfHours]
var LowerBound = 'Categories of IDs'[SumLowerBoundInclusive]
var UpperBound = 'Categories of IDs'[SumUpperBoundExclusive]
var RawOutput = int( LowerBound <= Hours && Hours < UpperBound )
var Output = if( RawOutput = 1, 1 )
return
Output
)
// You should have a Calendar table in your model
// with all the chunks of time that you desire.
// This is BEST PRACTICE and please stick to it
// if you don't want to have problems along the way.
// Once you've got this, you can write a measure...
// First, create a disconnected table in the model that'll
// store the categories, like so:
[Categories of IDs] = // calc table
SELECTCOLUMNS(
{
("Sum Of Hours in [0, 10)", 0, 10),
("Sum Of Hours in [10, 20)", 10, 20),
("Sum of Hours in [20, ∞)", 20, 10000000000)
},
// Exposed column for categeries of the ID's
"Category", [Value1],
// Technical hidden column
"SumLowerBoundInclusive", [Value2],
// Technical hidden column
"SumUpperBoundExclusive", [Value3]
)
// Then the measure... which will work for
// any piece of time, not only months.
[# IDs] =
SUMX(
CROSSJOIN(
'Categories of IDs',
ADDCOLUMNS(
VALUES( YourTable[Id] ),
"@SumOfHours",
CALCULATE( SUM( YourTable[Hours] ) )
)
),
var Hours = [@SumOfHours]
var LowerBound = 'Categories of IDs'[SumLowerBoundInclusive]
var UpperBound = 'Categories of IDs'[SumUpperBoundExclusive]
var RawOutput = int( LowerBound <= Hours && Hours < UpperBound )
var Output = if( RawOutput = 1, 1 )
return
Output
)
Hi,
I copied your Data. My table name is Feuil 7
Here is the 3 measures :
@Anonymous
Just want to point something out.. The problem with your solution is that it's not configurable through a table and when one will want to add another category, one will have to create another measure and update all visuals. This is not how it should be done because it can become a headache in the future. To do it properly one has to create a table of categories and then ONE AND ONLY ONE MEASURE that'll depend on the categories (please see my solution for details). Another problem with the solution above is that it's not aggregatable.
Understood, in my case, with @Anonymous is enough but i'll apply yours as well in case of further requirements.
Thank you both.
Thanks man, works perfectly
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |