Values from database are not displayed in PowerBI

[CKa]

Hi everyone,

I've only been working with PowerBI for a very short time and am now I am faced with a problem that I'm completely at a loss for:

I added a key figure (NetSalesPreviousYear) in my base DB. This key figure is also available and filled in SSAS

(Measure definition Net Sales previous year:= sum(Sales[NetSalesPreviousyear])):

In PowerBI I don't see any values in the new column after updating - also if I only select the very columnand nothing else

And PowerBI has been updated and has also changed (I adjusted a description field and the new description is now in PowerBI)

Has anybody an idea? I am thankful for every hint!!!!

[CKa]

I've now experimented a bit: If I build a new measure on an existing column, values are displayed in PowerBI.

If I now create a new column in the underlying database table and create a new measure in SSAS with this column, then the measure in PowerBI is empty. Is it possible that I need to introduce new relational attributes to PowerBI first? And if the answer is yer: How do I do that?

Thanks a lot!!!

[v-jtian-msft]

Hi,@CKa.I am glad to help you.
Hello,@amitchandak ,thanks for your concern about this issue.

Your answer is excellent! And I would like to share some additional solutions below

I tried to reproduce your problem by creating a SSAS tabular model in visual studio and creating a measure with sum function in it, deploying it to SSMS and then making live connections through powerBI.

Unfortunately I did not succeed in reproducing your problem, all the measures are displayed correctly and the correct values are calculated even after adding external slicer filter fields.
You also mentioned that your measure only sums the Sales table [NetSalesPreviousyear].

Here is my test:

Measure 1:=SUM(Table_1[Time])
Measure 2:=SUM('Table_1'[time 2])
CalculatedTable 1 =VALUES(Table_1)
Measure 3:=SUM('CalculatedTable 1'[Time])

So I have a couple of questions: are you creating multiple measures and only Measure in these measures
definition Net Sales previous year:= sum(Sales[NetSalesPreviousyear]) displays normally when SSAS taular model is created, but after connecting to power bi
Null values are displayed.
Does this measure depend on the results of other measures or is it a direct calculation?
You can try to re-create a simple demo with the problematic measure alone and then re-connect it with power BI Desktop to see if there is a problem.

Here are my recommended directions for checking:
1. Check if the data source is connected properly, make sure the connection between power BI and AS model is normal and stable.
2. Check if there are any problems with the measure formula: although the measure you provided is very simple, check that the correct data (fields) are referenced.
3. Make sure the data model is updated
4. check if there are data access permissions: if you have set access permissions in the AS model (RLS/OLS in AS) it is very important to set access permissions as these settings affect the scope of the data that the user can access and use, and if the user role does not have access to the field in your measure, the metrics may show up as null!
In fact this is the most important point to check in my opinion, please check in detail if you have these settings in your model
5. Check if your measure may have specific access rights set up, without being granted permission to view these metrics, then these metrics will also show up as null in PowerBI.
Ensure that the account you are using in PowerBI has sufficient permissions to access and read all relevant data and metrics in the SSAS model. Insufficient permissions may result in a situation where you cannot see the data

If possible, could you provide more screenshots of the hidden private information, which would help to identify the problem.
Looking forward to your reply

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CKa]

Thanks for your reply! And sorry for posting my general addition into your post - I am a greenhorn  🙂

To your points:

1. Check if the data source is connected properly, make sure the connection between power BI and AS model is normal and stable. --> The connection is working proberly in both directions (new added columns in the data source are displayes in SSAS an the changes made in SSAS are published to PowerBI (I change the descriptionfield of some measures to verify this)
2. Check if there are any problems with the measure formula: although the measure you provided is very simple, check that the correct data (fields) are referenced. --> what kind of problems might there be? I copied the formular for an other measure (netsales)
3. Make sure the data model is updated  --> it is (see point 1)
4. check if there are data access permissions: if you have set access permissions in the AS model (RLS/OLS in AS) it is very important to set access permissions as these settings affect the scope of the data that the user can access and use, and if the user role does not have access to the field in your measure, the metrics may show up as null! --> this migth be. How can I check and change this? I have the impresseion that new relational attributes are not introduced to PowerBI since I tried it with an other attribute (see first reply)  ...
In fact this is the most important point to check in my opinion, please check in detail if you have these settings in your model
5. Check if your measure may have specific access rights set up, without being granted permission to view these metrics, then these metrics will also show up as null in PowerBI. --> this migth be. How can I check and change this? I have the impresseion that new relational attributes are not introduced to PowerBI since I tried it with an other attribute (see first reply)  ...

[v-jtian-msft · Accepted solution]

Hi,@CKa .Thank you for your reply.

Based on your tests, I think it should appear in the AS model itself
You can check if your model has RLS/OLS set up by doing the following:

You can check if RLS/OLS is set by going to Models>Roles in the tabular model.
Here is the documentation, hopefully it will help you.
URL:

Dynamic row-level security with Analysis services tabular model - Power BI | Microsoft Learn

If you can't edit the current model because of data privacy, I recommend you to transfer the problematic measure to power BI desktop for creation.
This avoids the problematic measures in the AS model to be displayed as empty, which can solve your current problem and ensure the normal presentation of the report data.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CKa]

It worked at least - thank you!

[v-jtian-msft]

Hi,@CKa . Thank you for your reply.
You are welcome.

[CKa]

I've now experimented a bit: If I build a new measure on an existing column, values are displayed in PowerBI.

If I now create a new column in the underlying database table and create a new measure in SSAS with this column, then the measure in PowerBI is empty. Is it possible that I need to introduce new relational attributes to PowerBI first? And if the answer is yer: How do I do that?

Thanks a lot!!!

[amitchandak]

@CKa, Is it a column or measure? Measure using time intelligence like datesytd, sameperiodlastyear, perviousyear etc need a date in context to calculate these. I think you are using a measure. select some date, you should be able to get value.

Why DATESMTD is still blank, even after having the correct TI Setup: https://youtu.be/j0ug-XQgZtg

Why previousmonth does not give result when datesmtd is giving it: https://youtu.be/1KkoJehRVeg

[CKa]

Thanks for your reply.

Yes it is a measure. 

I tried it with combining the date-column at my first effert, The result is the same :