Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Arial12
Frequent Visitor

DAX - Related() Dynamically

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

 

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Arial12
Frequent Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.