Compartmentalise Data originating from one table - Use Measures, Query Editor or DAX?

[Anonymous]

Hi ,

I have questions around compartmentalisation of data from one common table. I want to understand the pros and cons of the possible options.  

I have following 

1. Source = OData Feed

2. Table = Item Ledger (It records all types of inventory transactions )

Objective:

Perform Sales, Purchase and Inventory Analysis

Solution Approach 1: 

1. Do not separate table and create your measures accordingly. 

My view: This will make the use of Quick Measures difficult for the users.

Solution Approach 2:

Create Calculated table using the CALCULATETABLE.

 My View: Will both the original and calculated table be in memory impacting performance?

Solution Approach 3:

Create table using Query Editor (duplicate the table) and then filter rows in M Code as below.

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Entry Type] = "Sale")) 

If I use this approach, I can split tables for Sales, Purchasing and other summarisations and have original table (Item Ledger) not load to Data Model and hence hide it from the data model and users alltogether.

The current problem I have at hand is that new table is also pointing to the original OData Feed (Hence downloading data twice).  How can I have this new table point to the other table? I have answered this question already.

Larger question that I have is: What are the pros and cons of each of the approach above?

Thanks in advance.

Devinder

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Actually, you mention two approaches: DAX or M code. Generally, we import and model data by using M code in Power BI, and we use DAX to create calculation. In your scenario, I would choose approach 3.

For more details about pros and cons of each approach, please review the following blog.
http://radacad.com/m-or-dax-that-is-the-question

Regards,

[Anonymous]

 Thanks you v-yuezhe-msft

Your reply is much appreciated; it cleared my doubts.

Regards

Devinder