Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I found problem on calculation with grouping filter depend on table name
The expected result of calculation separate into 2 groups depend on table name containing condition. And both have similar calculation but different only filter.
- Table Transaction need sum all in period (example monthly)
- Table Profile need pick record at max date only
Here is my code
VAR Table_Type = CONTAINS('Log Result','Log Result'[table_name],"txn")
RETURN
ROUNDDOWN(
CALCULATE(
IF(Table_Type,
//Transaction Table
1-DIVIDE(
CALCULATE(SUM('Log Result'[Value]), FILTER('Log Result','Log Result'[Condition] = "failed_record_count_excpt")),
CALCULATE(SUM('Log Result'[validated_total_row]), FILTER('Log Result','Log Result'[Condition] <> "failed_record_count_excpt"))
)
,
//Profile Table
1-DIVIDE(
CALCULATE(SUM('Log Result'[Value]), FILTER('Log Result','Log Result'[Condition] = "failed_record_count_excpt"), FILTER('Log Result','Log Result'[pos_dt] = MAX('Log Result'[pos_dt]))),
CALCULATE(SUM('Log Result'[validated_total_row]), FILTER('Log Result','Log Result'[Condition] <> "failed_record_count_excpt"), FILTER('Log Result','Log Result'[pos_dt] = MAX('Log Result'[pos_dt])))
)
)
)
,5)
By the way the result on display table as below attached, expectation result must be 99.643% at the first row that is one part of "txn" table. But actually result shown 99.641% that came from record at max date.
Please give suggestion to solve problem.
Hi @Qootaro without model it is hard to spot issue. Did you check that you have value as in named FILTER function for that column marked red?
Proud to be a Super User!
Hi @Qootaro try code below (adjust VAR for max)
VAR Table_Type = CONTAINS('Log Result','Log Result'[table_name],"txn")
VAR __max_var=MAX('Log Result'[pos_dt])
RETURN
ROUNDDOWN(
CALCULATE(
IF(Table_Type,
//Transaction Table
1-DIVIDE(
CALCULATE(SUM('Log Result'[Value]), FILTER('Log Result','Log Result'[Condition] = "failed_record_count_excpt")),
CALCULATE(SUM('Log Result'[validated_total_row]), FILTER('Log Result','Log Result'[Condition] <> "failed_record_count_excpt"))
)
,
//Profile Table
1-DIVIDE(
CALCULATE(SUM('Log Result'[Value]), FILTER('Log Result','Log Result'[Condition] = "failed_record_count_excpt"), FILTER('Log Result','Log Result'[pos_dt] = __max_var)),
CALCULATE(SUM('Log Result'[validated_total_row]), FILTER('Log Result','Log Result'[Condition] <> "failed_record_count_excpt"), FILTER('Log Result','Log Result'[pos_dt] = __max_var))
)
)
)
,5)
Proud to be a Super User!
@some_bih thank you, i tried that but the result did not change.
Expectation for table which contain "txn" must calculate by row 10 but actual run thru at row 16. I don't know why and no idea with this mechanism.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |