Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi there,
I need help and advices especially the DAX syntax to achieve the following:
1. to lookup the value in Table 1 exist in the Table 2 (master data) and captured in Exist column of Table 1. For e.g. the sample data in 2 tables
Table 1 Table 2
Name Role Name Element Name Exist Category Score Role Name Element Name
Ann Role A Element A Yes Functional 1 Role A Element A
Matt Role B Element F No Functional 1 Role A Element B
Ann Role A Element B Yes Functional 0 Role B Element C
Ann Role A1 Element x Yes Non-Function 1 Role A1 Element x
Ann Role A Element D Yes Functional 0 Role A Element D
Ann Role A Element y Yes Functional 1 Role A Element y
Ron Role B Element G Yes Functional 1 Role B Element G
Role A1 Element z
Role A1 Element m
2. To get the average score for each person by their (Role, Exist = Yes and Category)/total elements in the role. Expected result for;
Ave score for Ann for Role A, Element Existed & is Functional = 2/5 => 40%
Ave score for Ann for Role A, Element Existed & is Non-Function = 1/3 => 33.3%
Ave score for Ron for Role B, Element existed & is Functional = 1/2 => 50%
Ave score for Matt for Role B, Element existed & is Functional = 0
Thank you heaps!!
Solved! Go to Solution.
Not sure I understand, but see attached.
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
[Exists] = // calculated column
var RoleName_ = Table1[Role Name]
var ElementName_ = Table1[Element Name]
var RoleElementCombinationExists =
COUNTROWS(
filter(
Table2,
Table2[Element Name] = ElementName_
&&
Table2[Role Name] = RoleName_
)
) > 0
return
if( RoleElementCombinationExists, "Yes", "No" )
[Average] = // measure
// If you start slicing and dicing, you'll
// figure out how this measure works.
AVERAGEX(
SUMMARIZE(
Table1,
Table1[Name],
Table1[Role Name]
),
var Numerator =
CALCULATE( SUM( Table1[Score] ) )
var Denominator =
CALCULATE(
SUM( Table1[Score] ),
ALLEXCEPT(
Table1,
Table1[Name],
Table1[Role Name]
)
)
return
divide( Numerator, Denominator )
)
Now that I've tried to implement this on a real model I realized that it makes no sense. Your calculations are not making sense. You'll have to be more precise, @alicelpn. Sorry!
Hi @alicelpn
I do not understand how you are calculating the averages. Can you elaborate on one of them, for instance:
Ave score for Ann for Role A, Element Existed & is Functional = 2/5 => 40%
Can you show which rows go into the 2 (numerator) and which into the 5 (denominator)? And the rationale behind it
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
my sincere apology for the mistakes made and confusion caused. i redo the expected result for the average score as below. thanks heap
See the attached file for a possible solution. Your percentages for Ann both seem wrong still. Should be 4/4 for RoleA and 1/3 for RoleA1. Anyway you can tweak the measure as needed.
Avg Score =
VAR numRowsTable2_ =
CALCULATE (
COUNT ( Table2[RoleName] ),
TREATAS ( DISTINCT ( Table1[RoleName] ), Table2[RoleName] )
)
RETURN
DIVIDE ( COUNT ( Table1[RoleName] ), numRowsTable2_ )
Exist =
VAR aux_ =
CALCULATE (
COUNT ( Table2[ElementName] ),
FILTER (
Table2,
Table2[ElementName] = Table1[ElementName]
&& Table2[RoleName] = Table1[RoleName]
)
)
RETURN
IF ( aux_ > 0, "YES", "NO" )
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hello AIB,
Appreciated much your prompt response and help. The Exist work well, thank you.
My bad 🤦♀️ the formula for average = total score of Ann in Role A/total number of elements in Role A = 2/4 = 50% and to be appended in new table with the columns Name, Role Name, Average
Thanks 🙏🏻
Looks like there's still a mistake for Ann, RoleA1. Should be 1/3. Updated Measure and attached file
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
it works!!!! thank you very much 🤣😘. However, can you help me with the question below?
Not sure I understand, but see attached.
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |