Forum Discussion

GavinH's avatar
GavinH
Frequent Visitor
2 years ago

Measure error caused by calculated column

See additional information added and link to .pbix file in later post.   I am having problems with a measure and calculated column that seem to be interacting with one another in unexpected ways, p...
xifeng_L's avatar
xifeng_L
Super User
2 years ago

Hi GavinH ,

 

That's interesting. I need more info to locate the problem, can you provide the pbix file?

GavinH's avatar
GavinH
Frequent Visitor
2 years ago

Unfortunately, I do not appear to have the ability to attach a file in this forum. Not sure why that would be. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi GavinH ,

     

    Thanks for the reply from xifeng_L .

     

    As he said, screenshots and syntax alone can't help you much.

     

    You can upload the pbix file to a website like OneDrive, Google Drive, Dropbox, and then share the URL.

     

    Remember not to log in to your own account in Power BI Desktop when uploading the pbix file.

     

    If you have any other questions, please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    • GavinH's avatar
      GavinH
      Frequent Visitor
      2 years ago

      I have uploaded the .pbix file and the source Excel file to a Github repository here: https://github.com/ghammel/PBIBug/blob/main/README.md 

       

      And here is a restatement of the problem I am facing -

       

      I have encountered a problem in Power BI with using a calculated column [Fiscal Year] in combination with all of the following conditions:

      1. A measure that uses the ALL() function - both [Company share] and [Denominator] do this.
      2. A filter on the [Fiscal Year] column that filters out one or more values. (I filtered out values 2018-19 and 2019-20 for illustration.)
      3. A filter on another column - I used [Region] - that selects more than one, but not all values. (I selected values 111 and 113 for illustration.)

      The problem appears to be that the [Region] filter is interacting in an odd way with the measures. For each value of [Company], it is only calculating a grand total denominator only on the basis of the [Region] values that are present for that particular [Company] in that particular [Fiscal Year]. For example, in the 2020-21 Fiscal Year, companies C, F, and I have values for Region 113 but do not have values from Region 111. When both Regions are selected in the filter, the grand total calculation for these companies - [Denominator] - is only drawing from the rows with Region 113, which results in incorrect percentage [Company share] calculations.

       

       

       

      Despite these obvious errors, there seems to be no problem with the [Denominator] and [Company share] measures themselves. In fact, when I use an alternate Fiscal Year column created in Power Query rather than DAX (entitled [FiscalYrQuery]), the measures act as expected and calculate the grand total denominator properly.

       

       

       

      If anyone else has encountered this issue, I would appreciate hearing about it. Also, if anyone knows how to contact the Product Development or Quality Review teams, that would be helpful. I have spent weeks back-and-forth with Power BI Customer Support with nothing to show for it.

       

      (PLEASE NOTE: I am aware that there are other ways to fix this issue for this particular instance - not using a DAX calculated column, for example. My concern here is more general - that this is a core functionality that should work properly in Power BI but does not appear to do so.)