new measure to filter different purpose but same name

[xzeng2]

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!

[Icey · Accepted solution]

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.

[xzeng2]

Hi Colacan,

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

thanks

zeng

[xzeng2]

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

[colacan]

@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. 

[xzeng2]

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

[colacan]

@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.

[xzeng2]

hi Colacan,

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

[colacan]

@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.

[colacan]

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

[colacan]

@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"?

[xzeng2]

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

[colacan]

@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?