powerbidaxdata-analysispowerbi-desktopbusiness-intelligence

Create dax query in query editor to split table into multiple tables

Given this type of table structure:

Counterparty  Product  Deal  Date          Value
foo           bar      Buy    01/01/24     10.00
foo           bar      Buy    01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
fizz          buzz     Buy    01/01/24     10.00
fizz          buzz     Buy    01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00

I need to create two more tables that look like this:

Counterparty  Product  Deal  Date          Value
foo           bar      Buy    01/01/24     10.00
foo           bar      Buy    01/01/24     10.00
fizz          buzz     Buy    01/01/24     10.00
fizz          buzz     Buy    01/01/24     10.00

AND

Counterparty  Product  Deal  Date          Value
foo           bar      Sell   01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00

I am new to power bi, but In the query editor how can I create two more tables based on some condition like shown above? I also need to preserve the main table. Is something like this possible in query editor?

Edit: I can run a python script using pandas like this:

import pandas as pd

df = pd.DataFrame(dataset)
df = df[df['DealType'].eq('Buy/Sell')]

But I want to know the DAX equivalent to this

Solution

  • Are you sure you want the query editor (not available in your model and used for ad hoc queries)? If so, it is the following:

    EVALUATE
FILTER('Table', 'Table'[Deal] = "Buy")

    If you want a table in your model, then create a calculated table as

    FILTER('Table', 'Table'[Deal] = "Buy")