Forum Discussion
Removing total value in report
How can I remove total value in report with following dax code:
select_identity:=
Var identity = CALCULATE(FIRSTNONBLANK(VALUES('identity'[identity description]);1))
Var identitydb= CALCULATE(FIRSTNONBLANK(VALUES('identitydb'[identitydb description]);1))
Var Result= SWITCH(
TRUE();
identity = Blank();identitydb;
identity
)
Return IF(HASONEVALUE('Fact_table'[identitykey]) && HASONEVALUE('Fact_table'[identitydbKey]);Result;BLANK())
I've changed columnname.
I've two dimension tables (identity och identitydb) which has relation to fact table(Fact_table). If one of columns 'identity description' and 'identitydb description' is null it will return the value of the other. It works fine if one of them is null but it doesn't work when both of them has value and shows value in total in report.
Anonymous
You can use ISINSCOPE for this.
The measures I'm using in this example are:
1) your calculation
Switch = VAR Identity = MAX('Sample'[Identity]) VAR dbident = MAX('Sample'[Indetitydb]) RETURN IF( ISBLANK(Identity), dbident, Identity)2) Measure to remove total:
Remove total = IF(ISINSCOPE('Sample'[Index]), [Switch])To get his:
6 Replies
Anonymous
You can use ISINSCOPE for this.
The measures I'm using in this example are:
1) your calculation
Switch = VAR Identity = MAX('Sample'[Identity]) VAR dbident = MAX('Sample'[Indetitydb]) RETURN IF( ISBLANK(Identity), dbident, Identity)2) Measure to remove total:
Remove total = IF(ISINSCOPE('Sample'[Index]), [Switch])To get his:
Anonymous , you want to remove always or any ?
Return IF(HASONEVALUE('Fact_table'[identitykey]) || HASONEVALUE('Fact_table'[identitydbKey]);Result;BLANK())
Thank you amitchandak I want to remove it always, in the following screenshot I want to remove 'Fabric chocolate AB in Total row:
I tested with '||' but it still returns the value and shows 'identity discription' in Total row.
Anonymous , I think this how be driven by is there in row or matrix or table. Are these two columns are also in the row of matrix/table
