CUBEMEMBER not returns #N/A for something that doesn't exists in the model

%3CLINGO-SUB%20id%3D%22lingo-sub-2279250%22%20slang%3D%22en-US%22%3ECUBEMEMBER%20not%20returns%20%23N%2FA%20for%20something%20that%20doesn't%20exists%20in%20the%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279250%22%20slang%3D%22en-US%22%3E%3CP%3EI%20created%20a%20Pivot%20Table%20by%20getting%20data%20from%20Azure%20Analysis%20Services.%3CBR%20%2F%3EWhen%20I%20write%20a%20CUBEMEMBER%20with%20anything%20that%20doesn't%20exist%20in%20the%20tabular%20model%2C%20like%20%22anything%22%3A%3CBR%20%2F%3E%3DCUBEMEMBER(%22PRD-EDPModel%22%3B%20%22%5BFCT_FIN_CONSOLIDATED%5D.%5BNM_REPORTED_RECURRENT%5D.%26amp%3B%5Banything%5D%22)%20it%20returns%20%23N%2FA%3CBR%20%2F%3Ewhen%20I%20write%20something%20that%20exist%20in%20the%20tabular%20model%20it%20returns%20the%20value%3A%3CBR%20%2F%3E%3DCUBEMEMBER(%22PRD-EDPModel%22%3B%20%22%5BFCT_FIN_CONSOLIDATED%5D.%5BNM_REPORTED_RECURRENT%5D.%26amp%3B%5BCESE%5D%22)%20it%20returns%20%22CESE%22%3CBR%20%2F%3EBut%20when%20I%20write%20something%20that%20already%20existed%20in%20the%20past%2C%20but%20now%20doesn't%20exist%20anymore%20in%20the%20tabular%20model%2C%20it%20returns%20the%20%22CESE%22!!%3C%2FP%3E%3CP%3E%3DCUBEMEMBER(%22PRD-EDPModel%22%3B%20%22%5BFCT_FIN_CONSOLIDATED%5D.%5BNM_REPORTED_RECURRENT%5D.%26amp%3B%5BPersonnel%20Costs%20Adjustments%5D%22)%3C%2FP%3E%3CP%3EIt%20must%20returns%20%22%23N%2FA%22%20as%20well!!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Tela%20excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274112i2EEEAFA930057A59%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Tela%20excel.png%22%20alt%3D%22Tela%20excel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2279250%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2280133%22%20slang%3D%22en-US%22%3ERe%3A%20CUBEMEMBER%20not%20returns%20%23N%2FA%20for%20something%20that%20doesn't%20exists%20in%20the%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2280133%22%20slang%3D%22en-US%22%3EI%20notice%20that%20if%20I%20change%20%26amp%3B%20to%20ALL%20it%20works%2C%20but%20I%20didn't%20understand%20the%20logic%3CBR%20%2F%3E%3CBR%20%2F%3E%3DCUBEMEMBER(%22EDPModel%22%3B%20%22%5BFCT_FIN_CONSOLIDATED%5D.%5BNM_REPORTED_RECURRENT%5D.%5BALL%5D.%5BPersonnel%20Costs%20Adjustments%5D%22)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20like%20to%20understand%20cause%20I%20need%20to%20justify%20this%20change%20that%20my%20customer%20must%20to%20do%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2279385%22%20slang%3D%22en-US%22%3ERe%3A%20CUBEMEMBER%20not%20returns%20%23N%2FA%20for%20something%20that%20doesn't%20exists%20in%20the%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279385%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3CP%3ENo%2C%20I%20mean%20that%20I%20make%20the%203%20queries%20on%20the%20same%20sheet%20after%20getting%20data%20from%20Azure%20Analysis%20Services%2C%20so%20the%20data%20are%20refreshed%2C%20without%20the%20%22%3CSPAN%3EPersonnel%20Costs%20Adjustments%3C%2FSPAN%3E%22%20value%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCUBEMEMBER(%22PRD-EDPModel%22%3B%20%22%5BFCT_FIN_CONSOLIDATED%5D.%5BNM_REPORTED_RECURRENT%5D.%26amp%3B%5BPersonnel%20Costs%20Adjustments%5D%22)%20that%20doesn't%20exist%20on%20the%20tabular%20model%2C%20it%20returns%20%22CESE%22%2C%20not%20%22%23N%2FA%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2279330%22%20slang%3D%22en-US%22%3ERe%3A%20CUBEMEMBER%20not%20returns%20%23N%2FA%20for%20something%20that%20doesn't%20exists%20in%20the%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1031767%22%20target%3D%22_blank%22%3E%40aliceaguiaraac%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20mean%20it%20returns%26nbsp%3B%3CSPAN%3E%22CESE%22%20in%20second%20case%20after%20refresh%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2306021%22%20slang%3D%22en-US%22%3ERe%3A%20CUBEMEMBER%20not%20returns%20%23N%2FA%20for%20something%20that%20doesn't%20exists%20in%20the%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2306021%22%20slang%3D%22en-US%22%3EI%20notice%20that%20if%20I%20change%20%26amp%3B%20to%20ALL%20it%20works%2C%20but%20I%20didn't%20understand%20the%20logic%3CBR%20%2F%3E%3CBR%20%2F%3E%3DCUBEMEMBER(%22EDPModel%22%3B%20%22%5BFCT_FIN_CONSOLIDATED%5D.%5BNM_REPORTED_RECURRENT%5D.%5BALL%5D.%5BPersonnel%20Costs%20Adjustments%5D%22)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20like%20to%20understand%20cause%20I%20need%20to%20justify%20this%20change%20that%20my%20customer%20must%20to%20do.%3CBR%20%2F%3E%3CBR%20%2F%3EAnyone%3F%3C%2FLINGO-BODY%3E
New Contributor

I created a Pivot Table by getting data from Azure Analysis Services.
When I write a CUBEMEMBER with anything that doesn't exist in the tabular model, like "anything":
=CUBEMEMBER("PRD-EDPModel"; "[FCT_FIN_CONSOLIDATED].[NM_REPORTED_RECURRENT].&[anything]") it returns #N/A
when I write something that exist in the tabular model it returns the value:
=CUBEMEMBER("PRD-EDPModel"; "[FCT_FIN_CONSOLIDATED].[NM_REPORTED_RECURRENT].&[CESE]") it returns "CESE"
But when I write something that already existed in the past, but now doesn't exist anymore in the tabular model, it returns the "CESE"!!

=CUBEMEMBER("PRD-EDPModel"; "[FCT_FIN_CONSOLIDATED].[NM_REPORTED_RECURRENT].&[Personnel Costs Adjustments]")

It must returns "#N/A" as well!!

Tela excel.png

3 Replies

@aliceaguiaraac 

Do you mean it returns "CESE" in second case after refresh?

Hello @Sergei Baklan !

No, I mean that I make the 3 queries on the same sheet after getting data from Azure Analysis Services, so the data are refreshed, without the "Personnel Costs Adjustments" value:

 

=CUBEMEMBER("PRD-EDPModel"; "[FCT_FIN_CONSOLIDATED].[NM_REPORTED_RECURRENT].&[Personnel Costs Adjustments]") that doesn't exist on the tabular model, it returns "CESE", not "#N/A".

I notice that if I change & to ALL it works, but I didn't understand the logic

=CUBEMEMBER("EDPModel"; "[FCT_FIN_CONSOLIDATED].[NM_REPORTED_RECURRENT].[ALL].[Personnel Costs Adjustments]")

I would like to understand cause I need to justify this change that my customer must to do.

Anyone?