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

Question

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])

Are you required to do this in dax or can you use any database? I can help you understand the database structure, logic and terms in a relational database, but I do not know dax. — JustBeingHelpful
– JustBeingHelpful, Commented Apr 15, 2023 at 7:24
I want do in DAX . I want to know how to apply distinct value function for a table for getting distinct values from the filtered column not from the entire column. Currently my table measure is getting values I wanted. But not respecting to date slicer in sales order table. — karen
– karen, Commented Apr 15, 2023 at 7:31
Take a look at my notes. 99.9% of the programmers out there know SQL and not DAX. So if you can explain the SQL, you might find the equivalent with a google search. Sorry I couldn't help more. — JustBeingHelpful
– JustBeingHelpful, Commented Apr 15, 2023 at 7:47
SQL syntax of "DISTINCT ORDER_ID has the DAX syntax of DISTINCT ( SALES_ORDER[ORDER_ID] ) — JustBeingHelpful
– JustBeingHelpful, Commented Apr 15, 2023 at 7:57
I haven't tag SQL for this post. I need only a DAX modification. I want approach this using power bi only. Thanks — karen
– karen, Commented Apr 15, 2023 at 8:00

JustBeingHelpful · Accepted Answer · 2023-04-15 08:50:40Z

0

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'

edited Apr 15, 2023 at 8:50

answered Apr 15, 2023 at 8:38

JustBeingHelpful

19.1k39 gold badges169 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related