How to write the below case statement in mdx.I moved the below mentioned columns in the fact table.
sum(case when IsSummary = 1 and IsAudited = 1 and FinalAuditFlag = 1 then 1
else 0 end) AuditsCompleted,--Count - Completed Audits
i tried the below MDX query.
WITH
MEMBER [Measures].[count]
AS
(
case ([Measures].currentmember )
when 0 then ([Measures].[Is Summary] )
else 1
end
)
Select
{[Measures].[count]} on 0,
[Dim_table1].[TableId].members on 1
from [Dsv]
What you have done is almost correct. Just change
.currentmember
to an actual measure in your cube. Currently when you have the following it is referring to itself i.e.currentmember
by the black arrow it referring to the measurecount
by the black arrow...This is in
AdvWrks
:It returns this:
If I want to replace the empty cell for Components the we can use
CASE
:This now returns this:
IIF
is used a lot more inMDX
thanCASE
-IIF
is nearly always faster. So the above equivalent usingIIF
is the following: