topic Re: new measure to filter different purpose but same name in DAX Commands and Tips

new measure to filter different purpose but same name

xzeng2 — Sun, 03 Oct 2021 15:16:22 GMT

Hi experts,

suffering to solve below.

there are data from an online server dataset, which cannot process the data by power query locally. data like below, need to process as below:

1, remove old day record and keep most current date records only, red highlighted

2, need to remove the duplicate records in same date yellow and orange highlighted

3, compare the name and purpose, if same name with both "IN" and "OUT" purpose, then need to filter out them

4,final result just keep names with purpose "IN" but without purpose "OUT", blue highlighted.

5,ignor name only with purpose "OUT", green highlighted.

1, already use datediff to filter out old day record.

2, can use sort by date and time and buffer to remove the duplicate items if can process with power query, but how can i process this by DAX code?

3, how to compare those with same name but different purpose by DAX?

Appreciate kindly help with solution!

Re: new measure to filter different purpose but same name

colacan — Sun, 03 Oct 2021 21:11:04 GMT

xzeng2 Hi,

Since you are filtering Date, you need to make calculate column of "Date" in your talbe without time.

Date = Date(year(YourTable[DateTime]),month(YourTable[DateTime]),day(YourTable[DateTime]))

Then, create a table using below code:

TargetTable =

VAR LastestDate = MAX( YourTable[Date] )

VAR NamesWoOUT = SUMMARIZE(

FILTER( YourTable, YourTable[Purpose] = "IN" ),

YourTable[Name])

VAR LastDateTable = FILTER( YourTable, YourTable[Date] = LastestDate )

VAR Finaltable = FILTER(

ADDCOLUMNS(

SUMMARIZE(LastDateTable,

YourTable[Name], YourTable[Purpose], YourTable[Date]),

"Item", CALCULATE( MIN( YourTable[Item] ) )

YourTable[Name] IN NamesWoOUT

)

RETURN Finaltable

Maybe this is not complete table satisfiying your condition because I am not clear with bleow line

"compare the name and purpose, if same name with both "IN" and "OUT" purpose, then need to filter out them"

Do you need to delete all recodes of which the name with both "IN" & "OUT" or just keep recode with "IN"?

Re: new measure to filter different purpose but same name

xzeng2 — Mon, 04 Oct 2021 02:22:46 GMT

Hi Colacan,

Thank you for your propose, just filter out not delete the records with both "IN" and "OUT".

also could you please help to check if can filter out that one without "IN" but with "OUT" record?

Thanks and Regards

Zeng

Re: new measure to filter different purpose but same name

colacan — Mon, 04 Oct 2021 20:06:31 GMT

xzeng2 Hi, if you share the final table from the sample data above, it would be helpful to understand

Re: new measure to filter different purpose but same name

xzeng2 — Tue, 05 Oct 2021 14:13:32 GMT

Hi Colacan,

as per mentioned above, original sample data as below.

1, need to filter out item 1 since old date.

2, remove yellow highlighted (item 2,3,4,16,19,20,21,36 and 38) since duplicated input and remove the old one.

3, find out name with both "IN" and "OUT", make a filter to filter out those. (Item A-L)

4, list out with "IN" only (Item M,N,X,Y and Z)

5, list out with "OUT"only(item 39).

i met issue as below:

1, when convert date&time to date only, cannot recognize.

2, lastdatetable, mention refer to multiple columns error

3, final table, cannot recognize some of above measures

issue maybe due to the first date format.

Appreciate again for you quick responds.

Zeng

Re: new measure to filter different purpose but same name

colacan — Wed, 06 Oct 2021 09:52:28 GMT

xzeng2 You are correct. it is not Date type but text. It must be date type to solve your issue.

You may change the format from your source or edit it before you upload it using power query.

Re: new measure to filter different purpose but same name

colacan — Thu, 07 Oct 2021 12:51:12 GMT

xzeng2 Hi, I couldn't understand:

" just filter out not delete the records with both "IN" and "OUT"."

Do you want see them (the records with both "IN" and "OUT") in the table or not?

Re: new measure to filter different purpose but same name

xzeng2 — Thu, 07 Oct 2021 12:57:30 GMT

Hi Colacan,

I made the date measure successfully, but the date format still the same, any idea how to solve?

the data got from online forms data, cannot change the date format in the source.

appreciate!

zeng

Re: new measure to filter different purpose but same name

xzeng2 — Thu, 07 Oct 2021 13:20:52 GMT

Hi Colacan,

Yes, still need to show the list of records with both "IN" and "OUT" in the table.

thanks

zeng

Re: new measure to filter different purpose but same name

colacan — Thu, 07 Oct 2021 13:30:09 GMT

xzeng2 Hi,

Step1. Select [DateTime] at Filed pannel

Step2. Go to the "Column" tab on the ribbon

Step3. Change the format whichever you like

Thanks.

Please mark this as solution if this helped you. appretiate Kudo.

Re: new measure to filter different purpose but same name

xzeng2 — Thu, 07 Oct 2021 13:38:36 GMT

hi Colacan,

i have changed the format of the DATE and lastest date, but for the lastdatetable measure still showing below error.

Re: new measure to filter different purpose but same name

colacan — Thu, 07 Oct 2021 14:30:38 GMT

xzeng2 Hi,

the [Date] in your code means a table consists of all Dates from the Sheet1 table. hence you are comapring a whole dates with [lastes date] which power bi can not understand.

This is a concept related to row context and filter context which is not easy to catch.

if you are going to use this masure under unique [date] filter context - which means you will use this measure in a table which contains date column, you can change the [Date] to Max[Date].

Filter(sheet1, max([date]) = [lastest date]

Thanks,

Please mark this as solution if this helped you. appretiate Kudo.

Re: new measure to filter different purpose but same name

Icey — Wed, 20 Oct 2021 09:06:59 GMT

Hi xzeng2 ,

You can use filters like so:

Filter = VAR NameCount = CALCULATE ( COUNT ( 'Table'[Name] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Name] = MAX ( 'Table'[Name] ) ) ) VAR PurposeCount = CALCULATE ( DISTINCTCOUNT ( 'Table'[Purpose] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Name] = MAX ( 'Table'[Name] ) ) ) RETURN IF ( NameCount = 1 && PurposeCount = 1 && MAX ( 'Table'[Purpose] ) = "IN", 1 )

In addition, what the meaning of "ignore name only with purpose "OUT", green highlighted"? Do you mean all rules above won't apply on these records?

For more details, please check the attached .pbix file.

Best Regards,

Icey

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