SQL Server Performance Forum – Threads Archive
MDX statement for creating a calculated members
Fact table:<br /><br />RoomIDAgencyIDNo of unit emptyNo of unit rented<br /><br />100101 1 0<br />100201 1 0<br />100302 0 1<br />100402 0 1<br />100503 0 0<br /><br /><br />This fact table actually is showing a data related to the room is either rented or empty.<br />If the room rent out then the no of unit empty is 0 and the no of unit rented is 1 value.<br />If the room is empty then no of unit empty is 1 and the no of unit rented is 0 value.<br />If the room neither empty nor rented then both of the measure will be 0 value.<br /><br />At the cube, I created the calculated member <br /><br />a) ¡°No of empty¡± with this MDX :<br />Iif(Isempty([measures].[No of unit empty]),0, [measures].[No of unit empty])<br /><br />a) ¡°No of rented ¡± with this MDX :<br />Iif(Isempty([measures].[No of unit rented]),0, [measures].[No of unit rented])<br /><br /><br />The cube result shown as below:<br />AgencyID No of emptyNo of rented<br /><br />01 2<br />02 2<br />03<br />04 0 0<br />05 0 0<br /><br />Problems: [<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br />AgencyID for 01 of No of rented is empty<br />AgencyID for 02 of No of empty is empty<br />AgencyID for 03 of both No of empty and No of rented are empty.<br /><br />How to put the empty column to be shown as 0 value using the MDX statement for creating a calculated members?<br /><br />Thanks in advance[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br /><br /><br /><br /><br /> <br />
Try keeping [measures].[No of unit rented] directly for cube result instead of using calculated member.
I think using the direct measure should give you the right result.
The enchanced mdx will work finely as show as below after my testing: a) ¡°No of empty¡± with this MDX :
IIF(Iif(Isempty([measures].[No of unit empty]),0, [measures].[No of unit empty])=0,0,[measures].[No of unit empty])) a) ¡°No of rented ¡± with this MDX : IIF(Iif(Isempty([measures].[No of unit rented]),0, [measures].[No of unit rented])=0,0,[measures].[No of unit rented])
]]>