Post reply

Measures showing up Blank in Power BI

  • October 23, 2017 at 11:42 am

    #150653

    When I attempt to connect POwer BI Desktop to my tabular model, The measures all show up blank.  
    I am able to browse the model in excel from SSDT., and the measures do not show up blank.   
    When I look at the Values that the measures are calculated from in Power BI , the values can be used in a matrix until I attempt to use a dimension to split it into rows.  
    When I use the explore in excel from SSDT , The values in the tables cannot be used in the pivot table.  I am very confused as to what is going on with this.

  • October 23, 2017 at 12:47 pm

    #1964844

    I'm not following what the problem is. If you post your code for the measure, it may make it easier to see what's happening.

  • October 23, 2017 at 12:55 pm

    #1964847

    Further digging has shown this to only occur with measures on the Fact_transaction table.  
    This table has a relationship to dimCOA, but when I attempt to use these dimension and fact tables in powerBI they show up blank.  When looking at the tables this is based on in SSMS, the DIMCOA[COAKEY] field  and the Fact_Transaction[Accountkey] Fields work for doing a SQL JOIN.  I have atatched an image of the relationship between DimCOA and Fact_transaction in my tabular model. 
     
    Is something in this relationship set up incorrectly?

  • October 23, 2017 at 1:05 pm

    #1964850

    Try changing the relationship "Filter Direction" to two-way. This isn't necessarily the solution, but may help to troubleshoot the issue. In general, the way you have it set up is good. I'm just not sure about the queries being run so can't be more specific. Also, do you have a lot of rows? If so, double-check that you don't have some rows with data. You may just be showing blank rows along with data rows.

  • October 23, 2017 at 1:13 pm

    #1964853

    Brian Carlson - Monday, October 23, 2017 1:05 PM

    Try changing the relationship "Filter Direction" to two-way. This isn't necessarily the solution, but may help to troubleshoot the issue. In general, the way you have it set up is good. I'm just not sure about the queries being run so can't be more specific. Also, do you have a lot of rows? If so, double-check that you don't have some rows with data. You may just be showing blank rows along with data rows.

    After switching to both directions for the filtering, the relationship is still giving me any usable results.

  • October 23, 2017 at 1:19 pm

    #1964854

    Additionally, the measures on Fact_transaction work correctly with DimCOA When I use the explore with Excel option from the designer.

  • October 23, 2017 at 1:29 pm

    #1964855

    Are you running in Direct Query or In Memory mode?

  • October 24, 2017 at 9:23 am

    #1964960

    Brian Carlson - Monday, October 23, 2017 1:29 PM

    Are you running in Direct Query or In Memory mode?

    I am running in direct query mode.  After removing the tabular model from the server and re-deploying it fixed the issues of the measures turining up blank.  however, I now am encountering an issue with a measure that appears to be calculating correctly in Tabular Model Designer, but is giving errors when trying to use it in powerBI.  
    The DAX I am using for my measure is as follows 
    COGS(Pre R&D):=CALCULATE (SUM ( Fact_Transaction[GLL_Amount] ) , FILTER ( VALUES ( DimCoa[COA_Group] ) , DimCoa[COA_Group] IN {"Cost of Goods Sold" ,"Cost of Goods Sold - D&A" } ))

    When I look at it in the designer, I have a red underline on the DAX formula, but it does calculate out.  

    Within power BI  the error I receive is

    Failed to resolve name 'SYNTAXERROR'. It is not a valid table, variable, or function name.

  • November 19, 2018 at 7:40 am

    #2013957

    Hello, we are having the same error message with a similar query.   Were you able to resolve this issue?
    If so, what was the resolution?

  • November 19, 2018 at 8:55 am

    #2013969

    We were able to resolve the problem by changing the IN syntax to user ||

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply