Set multiple options to a Age Group column just in Power BI based on Age in datasource

[pavanmanideep]

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.

[pavanmanideep]

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.

[danextian]

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 )
    )

[pavanmanideep]

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.

[danextian]

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?

[pavanmanideep]

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.

[danextian]

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:

[pavanmanideep]

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.

[danextian]

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... 

[pavanmanideep]

@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. 

[danextian]

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"
)