How to prevent Power BI from filtering out rows where there are null or blank values in dataset

[KristinaT]

Hello!

I'm hoping someone can assist.   I have a list of account numbers in an excel sheet that I have loaded into Power BI.  I've then connected with a live data source that does not allow me to switch to import so that I can merge queries, etc.  I'm only able to select the data point I want from this live data source which is fine, except that Power BI keeps filtering out some of the account numbers from my original data set in the visual and I need to find a way to prevent that.   Regardless of whether Power BI returns resulting data from the live data set, I need to be able to keep the rows intact.   

Does anyone know of a way to do this?

Thank you!

[Anonymous]

Hi, @KristinaT 

Make sure that you set up a relationship between the Excel dataset and the live data source. This relationship should be account-based. If Power BI doesn't allow you to create a direct relationship because of a live connection, consider using a calculated table that references both sources. For more information about relationships, see Create and manage relationships in Power BI Desktop:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Visualize with DAX measures to create a DAX measure that counts or aggregates data from a live data source, while ensuring that all accounts in the Excel dataset are displayed. You can do this using a combination of , or functions. For example, you can create a measure that calculates the sum of transactions for each account, but make sure that all accounts remain in the visual, even if there aren't corresponding transactions in the live data source.

When you create a visual, use the account number in the Excel dataset as the axis or legend. Then, use the DAX measure that you created for the value. This setting should ensure that all accounts are visible, regardless of whether or not the corresponding data is available in the live feed.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[parry2k]

@KristinaT sorry it is not clear what you are trying to achieve. Could you please share some more details with examples?