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 Folks,
We were using Dynamics 365 CE as our datasource to our Power BI Report
I have to map following age groups in Power BI report based on age in datasource
18 - 39
40 - 59
40 and above
60 and above
I would need to set the age group in report; for example: if person's Age is 45, need to set both options 40 - 59 and 40 and above in a different column in power bi based on Age in my datasource.
Please note that I only have below values in my datasurce
18 - 39
40 - 59
60 and above
I tried different approaches with DAX expressions but couldnt find a way to acheive this. Could any one please guide me on this requirement in Power BI.
Hi @danextian ,
Thank you for replying me. I will give a bit more details about my problem, I have two different datasets.
DataSet 1
18 - 39
40 - 59
40 and above
60 and above
DataSet 2 which contains contacts age:
18 - 39
40 - 59
60 and above
There is a slicer based on Dataset 2, so my problem was to filter the contacts age in DataSet 1 which dont have 40 and above option based on slicer.
I.e if we select 40 and above in DataSet 1, it should list all the records in Age group 40- 59 and 60 and above also.
I can' think of any other way than to create a measure and use it in a visual
MyMeasure =
VAR _group =
SELECTEDVALUE ( Table1[GroupT1] )
RETURN
IF (
_group = "40 and above",
CALCULATE (
CALCULATE (
COUNTROWS ( Table2 ),
FILTER ( Table2, Table2[GroupT2] IN { "40 - 59", "60 and above" } )
),
CROSSFILTER ( Table1[GroupT1], Table2[GroupT2], NONE ) //removes the existing relationship between the two tables
),
COUNTROWS ( Table2 )
)
Proud to be a Super User!
Hi @danextian ,
Thank you for your reply, I have tried but unable to make it work...it gives me this error. Kindly suggest, there isnt any relationship between the two.
Thank you.
Hi @pavanmanideep ,
CROSSFILTER is used to modify the existing relationship between two tables. If there's none then it will return an error. Is there an existing relationship between the two?
Proud to be a Super User!
Thank you very much @danextian for your checking my issue. Yes the dataset 1 and dataset 2 doesnt have a relationship...basically I am trying to achieve the requirement by creating another dataset having age group option 40 and above which is not present in my datasource dataset.
But kindly suggest if there was some other way to acheive this requirement....can we create multioptionset in power bi datasets.?. Thanks a lot.
When you say dataset, are you referring to a table? A dataset is composed of one or more tables in a data model/in a pbix. Assuming table 1 (which contains the grouping with four items) contains unique values only, create a relationship from that table using the group column to the other table. Please see attached pbix for your reference:
Proud to be a Super User!
Hi @danextian ,
I am very sorry, I didnt get your approach, you were saying to create a relationship and removing the relationship in the DAX expression. In this case, will I be able to show all the 4 age groups from Group T1 shown in a slicer even though not present in my datasource, because ultimately I need to pull the records from the dataset which contains only GroupT2.
Hi
Have you had a look at the pbix I attached. If the approach there doesn't work, pelase provide a sanitized copy of your data model. Please refer to this post on how you can get a better/quick response:
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...
Proud to be a Super User!
@danextian For now I have changed this in datasource itself...since I couldnt get this through..as it gets bit tricky with different scenarios manipulating the data from another datasource and map to my actual dataset.Thank you very much for your assistance.
Hi @pavanmanideep ,
Try this as a calculated column in DAX:
Age Group =
SWITCH (
TRUE (),
'Table'[Age] >= 60, "60 and above",
'Table'[Age] >= 40, "40 - 59",
'Table'[Age] >= 18, "18 - 39 ",
"17 and below"
)
Proud to be a Super User!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |