Distinct of Categories names and Date

[dolevh]

Hi, 

I have 2 columns from other tables of categories names and date.

Example: 

Table1 [Date]	Table2 [Categories names]
1/1/2021	Sport
1/2/2021	Sport
1/3/2021	News
1/4/2021	News
1/5/2021	Computers

• I'm looking for a DAX that will bring me this table 

Table1 [Date]	Table2 [Categories names]
1/1/2021	Sport
1/3/2021	News
1/5/2021	Computers

Thanks for everyone!

[v-zhangti · Accepted solution]

Hi, @dolevh 

You say that the two columns interact with each other, and I have a date column in Table2 by default.

Measure =
CALCULATE (
    MIN ( Table2[Date] ),
    FILTER (
        ALL ( Table2 ),
        Table2[Categories names] = MAX ( Table2[Categories names] )
    )
)

If I don't understand correctly please correct me and tell me what the interrelationship between the two columns is like, thank you.

Best Regards,

Community Support Team _Charlotte

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

[v-zhangti · Accepted solution]

Hi, @dolevh 

You say that the two columns interact with each other, and I have a date column in Table2 by default.

Measure =
CALCULATE (
    MIN ( Table2[Date] ),
    FILTER (
        ALL ( Table2 ),
        Table2[Categories names] = MAX ( Table2[Categories names] )
    )
)

If I don't understand correctly please correct me and tell me what the interrelationship between the two columns is like, thank you.

Best Regards,

Community Support Team _Charlotte

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

[dolevh]

Thanks a lot, it's working!   

I want to add a condition for example: 

Date	Categories name
01/01/2021	Sport
01/02/2021	Sport
01/07/2021	Sport
01/08/2021	Sport
01/01/2021	News
01/02/2021	News
01/07/2021	News
01/05/2021	Computers
01/06/2021	Computers
01/07/2021	Computers
01/09/2021	Food
01/10/2021	Food
01/11/2021	Food

If I am looking for categories names from 01/05/2021 its will be:

Date	Categories name
01/05/2021	Computers
01/09/2021	Food

how it will be working in your DAX?  

@v-zhangti 

Thank you.

[v-zhangti · Accepted solution]

Hi, @dolevh 

Measure =
IF (
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Categories name] = MAX ( 'Table'[Categories name] )
        )
    )
        < DATE ( 2021, 5, 1 ),
    BLANK (),
    MIN ( 'Table'[Date] )
)

If there are other questions please start a new thread. Thank you!

Best Regards,

Charlotte Zhang

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

[dolevh]

Thank you!

[Fowmy]

@dolevh 

Not sure if you have the Date and the Category columns are in one table already. Please try the following new table.

Table New = 
ADDCOLUMNS(
    VALUES('Table-06'[Categories names]),
    "Date", CALCULATE( MIN('Table-06'[Date]))
)

[dolevh]

It's still not working 😞 

The columns from tables are different but there are interactions between them. 

@Fowmy