Distinct Count from different rows measure

[Zakhamido]

I need help. 

I have the below function and the use of the function is to count the number of matching rows from one table if the table record is matching between the 2 tables, and every thing is working fine except for one thing. 

Sometimes I will receive 2 rows from my data source for the same sub_name number which will mean that my function will count twice for every record I receive, and I want to let it count only distinct values for the Sub_name column to avoid duplication. 

which means for example if I receive the record of Sub_name = 123, twice and the number of matching records is 2 I want it to display 2 instead of 4 

My function is as following 

SUMX (
    ADDCOLUMNS (
        TIBCOINCIDENT_UPDATED,
        "__DCount",
            VAR CurrentSubName = TIBCOINCIDENT_UPDATED[SUB_NAME]
            VAR CurrentStatus = TIBCOINCIDENT_UPDATED[STATUS]
            VAR CurrentOutageTime = TIBCOINCIDENT_UPDATED[Outage_Time_UAE]
            VAR CurrentTime =
                NOW ()
            RETURN
                IF (
                    CurrentStatus = "Un-Planned Pending"
                        && 'TIBCOINCIDENT_UPDATED'[Exceeded] = "No",
                    CALCULATE (
                        COUNT ( 'Medical Case'[Acct Src Id] ),
                        'Medical Case'[Substation] = CurrentSubName
                    )
                ) + 0
    ),
    [__DCount]
)

Can any one help ? 

[some_bih]

Hi @Zakhamido I am not against creating measure for your issue, but did you try to make some transformation like, creating  TibcoIncident (can't be fixed at the moment) distinct per this part?

And later in Customer table connect this info and at end create measure.

[Zakhamido]

I am sorry , I didn't get your suggestion could you please clarify ?

[some_bih]

@Zakhamido did you think in model wise approach to connect tables and in one table add as calculated column data from other table ( if possible)?

[some_bih]

Hi  Try to replace 

 COUNT ( 'Medical Case'[Acct Src Id] )

with

DISTINCTCOUNT ( 'Medical Case'[Acct Src Id] ),

@Zakhamido

[Zakhamido]

@some_bih tried that already but nothing will change, I believe that it will only count the distinct in the 'Medical Case'[Acct Src Id]  for every matching condition, for example if I will receive Sub_name = 123 it will count distinct for that particular row and then it will evaluate the second match and count again, so distinct count will not do my requirment in this case as it is counting for every match in the sub_name  

[some_bih]

Hi @Zakhamido not enought info for some detailed reply, as model is not availabe, but do you need then COUNTX instead of SUMX if calculation logic is as you described.

[Zakhamido]

@some_bih I think it is better if I will explain my requirements in details so you can thankfully advise me on the approach,

I have 2 tables one in direct query and the other in import mode 

The first table is called TibcoIncident which is for outage affected substations data and the other table is called Medical Case which will have all the customers that is registered that Have a critical medical devices, now I want for every record to come in the TibcoIncident Table to check if this substation is available in the table 'Medical Case' and then count the number of affected customers from that table and then display the number of the measure in card 

Now I am able to fulfil my requirment with the function I mentioned but I am having an issue where sometimes I will receive a duplicate rows in the table TibcoIncident (can't be fixed at the moment) my function will count the matched customers twice as I do have 2 rows and I can't remove duplicates in the power query editor due to other requirements 

What I want is to count distinct Sub_name when the conditions match

Thanks again for helping