Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Issue with "Underlying Data Export ".


Hello,

 

We have a table visual with few measures represented as columns.

 

While exporting the data from a table visual using "Underlying Data" feature, Power bI was not flatenning the exported data. Instead it was only exporting the measure values. The below points have also been checked :

  • The underlying data source table do have UNIQUE KEY.
  • Show items with no data is not enabled.
  • The feature is not disabled by Administrator or by a report designer.

 

We are wondering what can be the cause that the "Underlying data" feature is not exporting the actual Underlying data.

 

Note : Export to "Underlying Data" feature is enabled in both POwer BI desktop file and service.

 

Any suggesstion or solution would be really appreciated.

 

Thank you

Need Help

8 Replies

  • sunnsonofindia's avatar
    sunnsonofindia
    Icon for Advocate II rankAdvocate II
    3 years ago

    Hey Anonymous & Others, Not sure whether you were able to resolve the issue, Eventhough it is too late now, posting the solution, so that it can help others.

     

    I referred the sample file which you shared to collinq and noticed you have kept the subjected measures in separate table (no worries it is the best way and everyone does including myself) but the problem arises from there because you reference the table name in the measure. To simplify - if you keep the measure out of its home table you need to reference the home table in the measure or else it is not going to work and and in the other case it is not going to give you correct export data using underlying data export option. 

     

    I see your measure is like 

     

    Total Calls =
    //Calculating discount of callids including Inbound and outbound calls
    DISTINCTCOUNTNOBLANK('Calls Data'[Call ID])
     
    if you change it to below and house it back to its home table, your problem will get resolved!
     
    Total Calls =
    //Calculating discount of callids including Inbound and outbound calls
    DISTINCTCOUNTNOBLANK([Call ID])
     
    Cheers!
     
    Hit like if this resolves your issue!
  • collinq's avatar
    collinq
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    I am not quite sure what you are striving for - when you export the data, you are exporting the data that is behind the visual.  So, the underlying data will provide the data that creates the visual and not necessarily every single data source in the dataset.  Also, if you have RLS applied this will affect the outcome.  And, if you have filters applied then that will affect the output as well.  Aggregrations are flattened and removed as well.  There is also a limit to the number of rows being exported and that might be a limit for you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi collinq ,

      I am trying to extract data from a table. The table contains Customer name, Year Month, and some measures (below screenshot). When I try to export “Underlying data ” from the table visual, it only exports the overall measure value and not the underlying data which are connected to it.

      EXPORTED FROM :

       

       

       

      EXPORTED RESULT 

       

       

      DATA MODEL

      • collinq's avatar
        collinq
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous ,

         

        Thanks for the diagram.  I can't recreate the problem.  In my tests, it is working fine and as expected.  Would you be willing to put the file in a file share somewhere and let me access it and see what I can do?  I test with and without filters, with and without the sum field (fyi - without the summation field [the 5469] being in the aggregration, you can't do underlying data).