HOW to count a column based on max , PLEASE HELP ME HELP MEE

[Anonymous]

Items.Id       MAX-Version       PENDING FOR OFFER
1653               2.0
1654               2.0
1654               1.0                  SIRAM SAI KRISHNA
1782               1.0

COUNT of Pending for offer should be 0, but if I count pending for offer which is not blank, Im getting " 1 "

To avoid duplicates I count Item.ID having pending for offer which is not blank 
Here I get count of distinct item_id filter by peding for offer which is not blank  I get count as "1"

According to Max-V version I should count Item_Id having Max versions and having pending for offer ,,, max version of ID 1652 is 2.0 which is blank for pending for offer ....... I should get count as 0 

I WANT HELP
HOW TO COUNT DISTINCT ITEM_ID HAVING MAX VERSION
FILTER BY PENFING FOR OFFER .... PLEASE HELP ME , IN ABOVE CASE MY ANSWER FOR COUNT OF PENDING FOR OFFER SHOULD BE 0 

[AllisonKennedy]

@Anonymous  

Please see attached file below signature. You may need to verify that your blank data is actually blank. Most efficient way is to clean this in Power Query using 'replace values'. The blanks should show as 'null':

[Anonymous]

Thanks for the reply, 

im having null for blanks 

[Anonymous]

IM sorry for Late Reply, I was sleeping.. 

I tried to create calculated colum after seeing error 

I creaded measure to check it 

also I created calculated colum with same above Max pendinf for offer 2 

IM getting Balnks, do it need to apply count on it ? 

[AllisonKennedy · Accepted solution]

@Anonymous  Yes, you need to apply a COUNT on the column. So the calculated COLUMN (not measure) should be: 

MAX Pending for Offer =

VAR _Id = 'Table'[Id]

VAR _MaxVersion = MAXX(FILTER('Table', 'Table'[Id] = _Id), 'Table'[Version])

RETURN

MAXX(FILTER('Table', 'Table'[Id] = _Id && 'Table'[Version] = _MaxVersion), 'Table'[Pending for Offer])

Then a new MEASURE:

Pending for Offer Count =

COUNT( 'Table'[MAX Pending for Offer])

Let me know what doesn't make sense.

[Anonymous]

THANK YOU SOMUCH ,, I GOT THE SOLUTION 🙂 🙂 

[AllisonKennedy]

@Anonymous  Yay! Glad we got there in the end. 

[Anonymous]

M PENDING FOR OFFER = var pfo= CALCULATE (

COUNTROWS ( VALUES ( Embedded_List[Items.Id] ) ),

FILTER (Embedded_List, Embedded_List[PENDING FOR OFFER] <> "" && [PENDING FOR OFFER] <> "NA")) return if(ISBLANK(pfo),0,pfo)  

above code im using will count ID which are having value in PENDING FOR OFFER, but i got wrong count because 

it is not filterering by max version 
 

item id 1654 has 2 versions 1.0 ,, 2.0  , i get count as 1 if i apply above CODE 2.0 has black that mean it should count blank by excluding 1.0 which has value 

can we created vertual table which give ID and MAX version of ID and PEDING FOR OFFER for the MAX version , then count on that would give solution ?

[vojtechsima]

Hi, @Anonymous,
Something like this can work:

MaxVersionNotBlankItems = 

var max_version = MAXX(ALL('Table'),'Table'[MAX_VersionNumber])
var countBlanks = COUNTROWS(FILTER('Table', ISBLANK('Table'[ PENDING FOR OFFER])=FALSE() && 'Table'[MAX_VersionNumber] = max_version))

return countBlanks+0

[Anonymous]

IM getting 0 for all the counts ?,  

I use below code to get my counts how ever , in perticular senario where ID 1654 version 2.0 is empty from pending for offer, count would be 0, but those of other who having max version and a value in PENDING FOR OFFER should count ,

M PENDING FOR OFFER = var pfo= CALCULATE (

COUNTROWS ( VALUES ( Embedded_List[Items.Id] ) ),

FILTER (Embedded_List, Embedded_List[PENDING FOR OFFER] <> "" && [PENDING FOR OFFER] <> "NA")) return if(ISBLANK(pfo),0,pfo)

I tried to do same funtion which should work on max version, it failed to give me 0 for ID 1654 ( max V 2.0 for 1654 has PFO empty, if it has a value on its MAX V it should count ) 

Basically we are counting PENDING FOR OFFER who has MAX version  , The reason Im counting ID is PENDING FOR OFFER may contain same name, there fore ID is uique ( counting ID where pending for offer <> blank } this should filter by MAX version )

[AllisonKennedy]

@Anonymous  The reason @vojtechsima 's solution isn't working is that it's not taking into account the current ID, so is just taking the MAX version for all IDs. Please try my method and do a count on that new calculated column that I suggested and let me know what issues you have with that solution (OPTION A) if any.

[AllisonKennedy]

@Anonymous  Two options.

OPTION A:

Create a new calculated column: 

MAX Pending for Offer =

VAR _Id = 'Table'[Id]

VAR _MaxVersion = MAXX(FILTER('Table', 'Table'[Id] = _Id), 'Table'[Version])

RETURN

MAXX(FILTER('Table', 'Table'[Id] = _Id && 'Table'[Version] = _MaxVersion), 'Table'[Pending for Offer])

OPTION B:

If you don't need the previous versions, use Power Query to remove duplicate values for ID, after sorting the data: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzNVbSUTLSMwCSSrE6YBETrCKGYJFgzyBHX4VgR08F7yDPYA8/R4gScwsjuBKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Version = _t, #"Pending for Offer" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Version", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Id"})
in
#"Removed Duplicates"