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.
Hi All,
Provided my dataset
Emp Id and Age is the orignial coulmns.
Age category(Bucket) is the column which was created with the help of Age Column.
Emp count is the measure i.e., Total emp counts.
Now i want to 2 more categories.
one that will sum up everything above 10 i.e., sum up the 3 categories (11 to 15 + 16 to 20 + 21 and Above)
another one that will sum up everything above 15 i.e., sum up the 2 categories (16 to 20 + 21 and Above)
Provided below my req. output in power bi
Please let me know how to achieve Updated Age Category column in power bi. i.e., i believe we need to create a calculated column in Power BI.
Thanks in Advance
Regards,
Prakash M
Solved! Go to Solution.
Hey @Anonymous ,
as one employee can be assigned to multiple age groups you enter the realm of many-to-many relationships.
For this reason, you have to adapt your data model.
I used this DAX statement to create a table called age groups:
age groups =
var agemax = MAX('Table'[Age])
return
UNION(
GENERATEALL(
ROW("age group" , "0 to 5" , "sort" , 1) , GENERATESERIES(0 , 5 , 1)
)
, GENERATEALL(
ROW("age group" , "6 to 10" , "sort" , 2) , GENERATESERIES(6 , 10 , 1)
)
, GENERATEALL(
ROW("age group" , "11 to 15" , "sort" , 3) , GENERATESERIES(11 , 15 , 1)
)
, GENERATEALL(
ROW("age group" , "16 to 20" , "sort" , 5) , GENERATESERIES(16 , 20 , 1)
)
, GENERATEALL(
ROW("age group" , "21+" , "sort" , 7) , GENERATESERIES(21 , agemax , 1)
)
, GENERATEALL(
ROW("age group" , "11 and above" , "sort" , 4) , GENERATESERIES(11 , agemax , 1)
)
, GENERATEALL(
ROW("age group" , "16 and above" , "sort" , 6) , GENERATESERIES(16 , agemax , 1)
)
)
I created another table I call age. This table is used only to create relationships between the employee | age table I call Table. The table age can be hidden. This is the DAX to create the age table:
age =
DISTINCT(
ALLNOBLANKROW('Table'[Age])
)
Then I create relationships , be aware of the filter direction Both between the age table and the age groups table:
This allows to simply create this table visual, no calculated columns or measures are needed:
There is one thing to note, as there is no direct relationship between the tables Table (Employee ID | Age) and age groups, you will be hinted that there is no direct relationship between the table, and it will seem that something did break. But as soon you change the aggregation function of the column Employee ID to Count (Distinct) everything will work as expected.
Personally I prefer to answer questions by the data model and avoid creating calculated columns or measure.
Regards,
Tom
Hey @Anonymous ,
as one employee can be assigned to multiple age groups you enter the realm of many-to-many relationships.
For this reason, you have to adapt your data model.
I used this DAX statement to create a table called age groups:
age groups =
var agemax = MAX('Table'[Age])
return
UNION(
GENERATEALL(
ROW("age group" , "0 to 5" , "sort" , 1) , GENERATESERIES(0 , 5 , 1)
)
, GENERATEALL(
ROW("age group" , "6 to 10" , "sort" , 2) , GENERATESERIES(6 , 10 , 1)
)
, GENERATEALL(
ROW("age group" , "11 to 15" , "sort" , 3) , GENERATESERIES(11 , 15 , 1)
)
, GENERATEALL(
ROW("age group" , "16 to 20" , "sort" , 5) , GENERATESERIES(16 , 20 , 1)
)
, GENERATEALL(
ROW("age group" , "21+" , "sort" , 7) , GENERATESERIES(21 , agemax , 1)
)
, GENERATEALL(
ROW("age group" , "11 and above" , "sort" , 4) , GENERATESERIES(11 , agemax , 1)
)
, GENERATEALL(
ROW("age group" , "16 and above" , "sort" , 6) , GENERATESERIES(16 , agemax , 1)
)
)
I created another table I call age. This table is used only to create relationships between the employee | age table I call Table. The table age can be hidden. This is the DAX to create the age table:
age =
DISTINCT(
ALLNOBLANKROW('Table'[Age])
)
Then I create relationships , be aware of the filter direction Both between the age table and the age groups table:
This allows to simply create this table visual, no calculated columns or measures are needed:
There is one thing to note, as there is no direct relationship between the tables Table (Employee ID | Age) and age groups, you will be hinted that there is no direct relationship between the table, and it will seem that something did break. But as soon you change the aggregation function of the column Employee ID to Count (Distinct) everything will work as expected.
Personally I prefer to answer questions by the data model and avoid creating calculated columns or measure.
Regards,
Tom
@Anonymous , you can create a new column using switch
switch( true()
[age]<= 5 ," 0-5",
[age]< 10 ,"06 10"
// keep on adding conditions
)
Hi @Anonymous ,
This sounds like dynamic segmentation is required.
Try following this guide at daxpatterns.com: Dynamic Segmentation
Pete
Proud to be a Datanaut!
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |