CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
karen
karen

Reputation: 63

How to create a new table in Power BI with only distinct values from parent table after filters applied

I have a table with two columns which contains date and sales order. I want to create an another table using DAX which contains distinct sales orders getting data from sales order column for making some relationships with other tables. I use below DAX code, but my issue is even after filtering the data from the sheet1 table my DAX table shows all the distinct sales orders from the sales order table. I want to get distinct sales order from filtered table if there any filter for the date.

Distinct Sales orders = DISTINCT(Sheet1[Sales Order])

enter image description here

Upvotes: 1

Views: 8365

Answers (1)

JustBeingHelpful
JustBeingHelpful

Reputation: 18990

This is an attempt, because I've never used DAX before today. I will delete if this doesn't work for you.

Using the SQL table name and column names from my notes below, we have this DAX syntax.

filterDate = DATE("2023", "4", "1")

EVALUATE
 (
    SELECTCOLUMNS (
        DISTINCT ( 'SALES_ORDER'[ORDER_ID] ),
        'SALES_ORDER'[ORDER_DATE]
    ),
    FILTER (
        'SALES_ORDER'[ORDER_DATE] = filterDate
    )
)

Using your DAX table name and DAX column names, we have this DAX syntax.

filterDate = DATE("2023", "4", "1")

EVALUATE
 (
    SELECTCOLUMNS (
        DISTINCT ( 'Sheet1'[SALES_ORDER] ),
        'Sheet1'[DATE]
    ),
    FILTER (
        'Sheet1'[DATE] = filterDate
    )
)

This was an attempt to convert SQL to DAX based on what the SQL query would look like.

SELECT
  DISTINCT ORDER_ID, ORDER_DATE
FROM
  SALES_ORDER
WHERE
  ORDER_DATE = '2023-04-01'

Upvotes: 0

Related Questions