PowerBI DAX Comparing two measures

Question

I am having an issue and cannot figure it out, since I am still pretty new on DAX. Currently, I am having a table in the database, where it stores the "Extracted" documents that have been automatically extracted value by the software. I also have the second step, during this process, called Validation - "VA02", the value can be manually changed or leave it as it came from "EX02".

I need to create a DAX measure that would return the distinct count of Document ID for the count of CALL_POINT = "VA02" that Values differ from the CALL_POINT = "EX02".

	DOCID	CALL_POINT	VALUE
1	654474	EX02
2	654474	EX02	44.57
3	654474	EX02	0.00
4	654474	VA02
5	654474	VA02	0.00
6	654474	VA02	0.00

The first row of EX02 corresponds with the first row of VA02, etc. There is a difference in lines 2 and 5, which means the value was changed manually. So the expected result, in this case, should be 1.

	DOCID	CALL_POINT	VALUE
1	654475	EX02	0.00
2	654475	VA02	0.00
3	654476	EX02	10.00
4	654476	VA02	15.00
5	654477	EX02	95.73
6	654477	VA02	95.00

In this example, the output should be 2. Since the value between EX02 and VA02 differs in DOCID 654476 and 654477.

score 0 · Answer 1 · answered Jun 27 '22 at 15:56

If I understand correctly, you want to compare the sum of VALUE, for each DOCID for specific CALL_POINT values.

My solution is to create a measure Check that calculate the sum of VALUE where type is "EX02" and "VA02". Then, to compare these two sums and return error when they are different.

The measure. Please note that the table is named "TMP" below:

TMP[Check] =
VAR SumEX02 =
CALCULATE(
    SUM(TMP[VALUE]),
    TMP[CALL_POINT] = "EX02"
)
VAR SumVA02 = 
CALCULATE(
    SUM(TMP[VALUE]),
    TMP[CALL_POINT] = "VA02"
)
VAR Result =
SumEX02 - SumVA02
RETURN
IF(
    NOT Result = 0,
    "Error",
    "Ok"
)

The, you can create a new table visualisation with columns :

DOCID
The new measure Check

Or run the following table expression:

NewTable =
ADDCOLUMNS(
    SUMMARIZE(
        TMP,
        TMP[DOCID]
    ),
    "Check",
    TMP[Check]
)

PowerBI DAX Comparing two measures

1 Answers1