CubeValue formula to data model - only works partially

[Kiwi_911]

Hello,

I created a simple comparison file using Power Query and Cubevalue formulas. In the background, files are read from folders and compiled into an overview called 'Periodenvergleich' I have loaded this into the data model and now want to analyse it by using CUBEVALUE formulas. Some cube elements should be variable, selected via a dropdown menu. The CUBEVALUE formula works so far, at least I select 'Bereich ABC' as the organizational unit (cell D2 in the sample file). However, when I change the organizational unit, no values appear, even though there are values in the data model for all organizational units. At first, I thought it might be a typographical error between the data model and the dropdown list, but it seems correct to me. Do you have any idea what the problem might be?

Thanks a lot for help.

Regards,

Kiwi

Hallo, ich habe mir einen kleinen Periodenvergleich über Power Query gebaut. Im Hintergrund werden Dateien aus Ordnern eingelesen und zu einer Übersicht "Periodenvergleich" zusammengebaut. Diese habe ich ins Datenmodell geladen und möchte nun per Cubewert-Formel flexibel darauf zugreifen.

Die variablen Cubeelemente sind per Listen-Menü auswählbar. Die Cubewert-Formel funktioniert auch soweit, zumindest wenn ich als Org-Einheit "Bereich ABC" (Zelle D2 in der Musterdatei) auswähle. Jedoch wechsle ich die Org-Einheit, kommen keine Werte mehr, obwohl im Datenmodell für alle Org-Einheiten Werte vorhanden sind. Ich dachte erst an einen Fehler in der Schreibweise zwischen Datenmodell und Liste, aber die passt meines Erachtens. Habt ihr noch ein Idee, woran es liegen könnte?

Danke und lieben Gruss,

Kiwi

[v-pnaroju-msft]

Thankyou, @pcoley for your response.

Hi @Kiwi_911,

We are pleased to note that your issue has been resolved. Thank you for sharing your insights and approach in resolving the issue, which will be beneficial to other members of the community. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[pcoley · Accepted solution]

@Kiwi_911 
The actual formula has an IFERROR surrounding the CUBEVALUE formula; it returns 0 if the CUBEVALUE formula evaluation returns an error.
Therefore, in the report the 0-values might correspond to errors in the CUBEVALUE calculations.
Please try evaluating it without the IFERROR and try to debug if there are errors; please check if you have in Measures the corresponding formulas for each Organizational Unit (and that they work with the "Summe von "&OrganizationUnitName).
If this does not work please share the formulas you have in [Measures] so we can try to find if there are some issues there.

[Kiwi_911]

Hello pcoley, thanks for your help. Yes, the error is caused by the CUBEVALUE formular. I can try without the IFERROR and its the same result. But based on your hint, I checked the concatenation "Summe von "&OrganizationUnitName and changed it. And YES, it works. 

changed formulaformer formula