DAX - Related() Dynamically

[Arial12]

Hello there Experts,

There are two tables, one with main data Table1 and another one is a dimension table which has different category Dim_cat and both these tables are linked.

PowerBI Desktop, i added a slicer for that dimension table for users to select different a category/s of their chioce and get a total etc

I did some DAX formula and create a measure called Total_LF on a TABLE1:

Total_LF = CALCULATE(COUNTROWS(FILTER('Table1',RELATED('Dim_CAT'[Category]) ="LOST and Found")),ALLEXCEPT(Table1,Table1[Male]))

Repeat = CALCULATE(DISTINCTCOUNT(TABLE1[MALE]),FILTER(TABLe1,TABLE1[Total_LF]>1))

this gives me what i need but how can i combine these two into one measure and then

i have hard coded for dim_cat[category], is there a way to make this more dynamic ? Otherwise i have to do it seperately for each categogy exists in dim_cat table and in future if a new category is added then it won't have a measure for it unless, it will be added manually..

[Ashish_Mathur]

Hi,

Please share the data file and also show the expected result.

[Arial12]

Hello there Experts,

There are two tables, one with main data Table1 and another one is a dimension table which has different category Dim_cat and both these tables are linked.

PowerBI Desktop, i added a slicer for that dimension table for users to select different a category/s of their chioce and get a total etc

I did some DAX formula and create a measure called Total_LF on a TABLE1:

Total_LF = CALCULATE(COUNTROWS(FILTER('Table1',RELATED('Dim_CAT'[Category]) ="LOST and Found")),ALLEXCEPT(Table1,Table1[Male]))

Repeat = CALCULATE(DISTINCTCOUNT(TABLE1[MALE]),FILTER(TABLe1,TABLE1[Total_LF]>1))

this gives me what i need but how can i combine these two into one measure and then

i have hard coded for dim_cat[category], is there a way to make this more dynamic ? Otherwise i have to do it seperately for each categogy exists in dim_cat table and in future if a new category is added then it won't have a measure for it unless, it will be added manually..