Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Can I get some help for DAX script
I created a scenario and it is just a simple count.
StudentID | Subject | MeasureName | MeasureValue |
1 | Chemistry | QuestionsAsked | 2 |
1 | Chemistry | QuestionsAnswered | 2 |
1 | Biology | QuestionsAsked | 1 |
1 | Biology | QuestionsAnswered | 1 |
2 | Chemistry | QuestionsAsked | 2 |
2 | Chemistry | QuestionsAnswered | 1 |
3 | Chemistry | QuestionsAsked | 1 |
3 | Chemistry | QuestionsAnswered | 1 |
3 | Biology | QuestionsAsked | 3 |
3 | Biology | QuestionsAnswered | 1 |
I wanted to count Total number of students whose MeasureValue for QuestionsAnswered for subject = Measure value QuestionedAsked for that subject
Answer will be
Total number of student subjects = 3
because Student ID 2 's Measure value for QuestionsAnswered (Chemistry) is not equal to Measure value QuestionAsked(Chemistry).
Similarly Student ID 3 's mesaure value for QuestionsAnswered (Biology) is not equal to Measure value QuestionAsked(Biology).
I have made those two students records bold to exclude but rest of three records should be compliant.
Can someone guide how a DAX can be written.
Thanks in advance.a
Regards
Solved! Go to Solution.
@Anonymous
Here is the measure and corrected calculation:
Status Measure =
SUMX(
Table1 ,
VAR __Q =
CALCULATE(
SUM(Table1[MeasureValue]),
Table1[MeasureName] = "QuestionsAsked",
ALLEXCEPT(Table1, Table1[StudentID], Table1[Subject] )
)
VAR __A =
CALCULATE(
SUM(Table1[MeasureValue]),
Table1[MeasureName] = "QuestionsAnswered",
ALLEXCEPT(Table1, Table1[StudentID], Table1[Subject] )
)
RETURN
IF( [MeasureName] = "QuestionsAsked" , IF( __Q = __A , 1 , BLANK()))
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
So kind of you @Fowmy
This DAX is very helpful but requirement is bit different.
So thankful for your support.
Regards
@Anonymous
Here is the measure and corrected calculation:
Status Measure =
SUMX(
Table1 ,
VAR __Q =
CALCULATE(
SUM(Table1[MeasureValue]),
Table1[MeasureName] = "QuestionsAsked",
ALLEXCEPT(Table1, Table1[StudentID], Table1[Subject] )
)
VAR __A =
CALCULATE(
SUM(Table1[MeasureValue]),
Table1[MeasureName] = "QuestionsAnswered",
ALLEXCEPT(Table1, Table1[StudentID], Table1[Subject] )
)
RETURN
IF( [MeasureName] = "QuestionsAsked" , IF( __Q = __A , 1 , BLANK()))
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Go to your table in Data view and add this code as a New Column:
Status =
VAR __A =
CALCULATE(
SUM(Table1[MeasureValue]),
Table1[MeasureName] = "QuestionsAnswered",
ALLEXCEPT(Table1, Table1[StudentID], Table1[Subject] )
)
RETURN
IF(
IF( [MeasureName] = "QuestionsAsked" , [MeasureValue] - __A, 0) = 0, BLANK(), [MeasureValue] - __A
)
You show it in the visual as
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group