Count of unmatched taskid as a measure

[Microblog321]

Hello friends,

I am new to power bi . I am looking for a measure which can count the unmatched taskid From 2 tables .we need to compare table 1 with table 2 if there is a unmatch in table 2 (taskid ))then count should be 1 . It should count 1 time as there are dupicates .these 2 tables have a relationship  with date table as 1 to many  . i also have a visul filter on date . So if  i filter dates between 1/jan to 30/mar it should give me a result as below .Please see below example and expected result.

table 1.                Table 2
taskid  date         Taskid. Date
1d    24/mar      
1d.    24/jan
1d    16/ july        
2.x    22/april        3.z      24/dec
3.z    24/apr          3.z      22/april
4p    24/feb       
4p    26/feb.          3.z.     24/dec

Expected result 

Taskid.     Count

1d             1

4p             1

       

Regards

[v-jayw-msft]

Hi @Microblog321 ,

Check the measure, filter needs to be at least page level filter.

Measure = 
var _mark = IF(SELECTEDVALUE(TableA[taskid]) in VALUES(TableB[Taskid]),1)
return
MAXX(TableA,_mark)

Best Regards,

Jay

[Microblog321]

Hello friend ,

thanks for your reply . I want to see values which are unmatched . 

which are not in table 2. Can you pease help

[v-jayw-msft]

Hi @Microblog321 ,

Modify the measure as below and it should work.

Measure =

var _mark = IF(SELECTEDVALUE('Table A'[taskid]) in VALUES('Table B'[Taskid]),BLANK(),1)

return

MAXX('Table A',_mark)

Best Regards,

Jay

[Microblog321]

Thank you sir,

this works for me perfectly .But just want to ask . Now i tried to put another dimension called city . But the count still coming out to be 1 for every city . Basically every city have many task id . Is there any way i can count the taskid for every city . I mean it can aggregate accordingly if any other dinesions i put like city , area or state .

[v-jayw-msft]

Hi @Microblog321 ,

That’s because we are using MAXX() in the formula, it will return the max value which is also 1.

You could use SUMX() or COUNTX() instead.

For example:

Measure =

var _mark = IF(SELECTEDVALUE('Table A'[taskid]) in VALUES('Table B'[Taskid]),BLANK(),1)

return

SUMX(ALLEXCEPT('Table A',[city],[area]),_mark)

Best Regards,

Jay

[Microblog321]

I am not getting the expected result can you please see.

Hello Sir, Please see the image . I have tried the changes as per your dax code. it doesnt seem to work . please see the image.

[Microblog321]

I am not getting the expected result can you please see.

Hello Sir, Please see the image . I have tried the changes as per your dax code. it doesnt seem to work . please see the image.

[Microblog321]

I am not getting the expected result

[Ashish_Mathur]

Hi,

Within your selected date range, task id "1d" appears in Table2.  Therefore, why should it appear in the result? 

[Microblog321]

Hello sir

Thanks for your reply. Taskid 1d is not present in the table 2.

Measure = 
var _mark = IF(SELECTEDVALUE(TableA[taskid]) in VALUES(TableB[Taskid]),1)
return
MAXX(TableA,_mark)

this measure seems to be working for me . But it selects all the matching value . Can you please make some changes in this measure where it can select only unmatched values . Like NOT in table 2 . Something like this

thank you

[Microblog321]

Can some one please help .

[Microblog321]

Taskid is text column will the max function work on text columns . Can you please help

[jeroendekk]

Hi @Microblog321 
I am assuming there is no relationship between both tables?
You could use a lookup function. You're measure would be 

VAR task =
    MAX ( Table1[taskid] )
RETURN
    IF ( NOT ISBLANK ( LOOKUPVALUE ( Table2[Taskid.], Table2[Taskid.], task ) ), 1 )

This works with the sample data and gives the expected results.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Jeroen

[Microblog321]

Hello Friend,

thanks for your help . I am sorry i have made some changes in my question. Can you please help me with the aboove query

regards

[jeroendekk]

@Microblog321 
If you want to count the unmatches instead of the matches remove the NOT operator from the function and I would still work.