Power BI Dynamic Calculation based on dynamic filter and date selection

[Anonymous]

Hi All,

I have the following scenario-

1) Age category (Adult, Child and You Adult)- It is coming from a table. The column is being used as a slicer:

Adult - age 26 and above (>26)

Young Adult - age 18 to 26 (>=18 and <=26)

Child - age less than 18 (<18)

2) I have a table with unique ID's and date of birth:

3) A third table (as a date dimension), which gives me sample dates that the user can select:

Requirement -

1) The user will select a date from the Day slicer and a category from  the category slicer.

2) Based on the day selection, the age calculation has to be dynamic and based on the category, the count of rows from the second table are to be returned.

For example - I select 06/06/2022 - based on this date, the age of the rows of the second table will be calculated, and I select category "Young Adult". Hence, the count will only be of rows which have age from 18 to 26.

I have tried to make it work but unsuccessfully. I would appreciate if someone could guide me to a solution if at all this is possible to do in PBI. Thank you.

Link to my PBI file - https://github.com/sommkh/pbi_age_test/blob/2cc9eb7dbe7788ea21da5ba1bb8c7f08adca60e2/Age_Test_Datase...

[v-yalanwu-msft · Accepted solution]

Hi， @Anonymous ；

You could create the measure as follow:

DIFF = CALCULATE (
        ROUNDDOWN (
            DATEDIFF ( MIN ( AGE_TEST_DATA[IND_BIRTH_DT] ), MAX('D_DATE'[DATE]), MONTH ) / 12,
            0))

count = 
SWITCH(MAX('AGE_CATEGORY'[CATEGORY]),"Adult",CALCULATE(COUNT('AGE_TEST_DATA'[ID]),FILTER('AGE_TEST_DATA',[DIFF]>26)),
"Young Adult",CALCULATE(COUNT('AGE_TEST_DATA'[ID]),FILTER('AGE_TEST_DATA',[DIFF]>=18&&[DIFF]<=26)),
"Child",CALCULATE(COUNT('AGE_TEST_DATA'[ID]),FILTER('AGE_TEST_DATA',[DIFF]>=0&&[DIFF]<18)))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.