Distinct Count from different rows measure

Question

I need help.&nbsp;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.&nbsp;&nbsp;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.&nbsp;&nbsp;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&nbsp;&nbsp;My function is as following&nbsp;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"
                        &amp;&amp; 'TIBCOINCIDENT_UPDATED'[Exceeded] = "No",
                    CALCULATE (
                        COUNT ( 'Medical Case'[Acct Src Id] ),
                        'Medical Case'[Substation] = CurrentSubName
                    )
                ) + 0
    ),
    [__DCount]
)Can any one help ?&nbsp;

some_bih · Answer

Hi&nbsp;&nbsp;Try to replace&nbsp;
 COUNT ( 'Medical Case'[Acct Src Id] )
with
DISTINCTCOUNT ( 'Medical Case'[Acct Src Id] ),

Zakhamido

zakhamido · Answer

some_bih&nbsp;tried that already but nothing will change, I believe that it will only count the distinct in the&nbsp;'Medical Case'[Acct Src Id]&nbsp; 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&nbsp;&nbsp;

some_bih · Answer

Hi&nbsp;Zakhamido&nbsp;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 · Answer

some_bih&nbsp;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&nbsp;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&nbsp;&nbsp;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&nbsp;&nbsp;What I want is to count distinct Sub_name when the conditions matchThanks again for helping

some_bih · Answer

Hi&nbsp;Zakhamido&nbsp;I am not against creating measure for your issue, but did you try to make some transformation like, creating&nbsp;&nbsp;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.

Forum Discussion

Distinct Count from different rows measure

7 Replies

Related content

Cumulative Difference from Another measure

count distinct

Different Distinct count

Different schema into different destination

Toggle between different measures