topic Re: Distinct Count from different rows measure in DAX Commands and Tips

Distinct Count from different rows measure

Zakhamido — Thu, 22 Feb 2024 09:51:43 GMT

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 ?

Re: Distinct Count from different rows measure

some_bih — Thu, 22 Feb 2024 10:24:31 GMT

Hi Try to replace

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

with

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

Zakhamido

Re: Distinct Count from different rows measure

Zakhamido — Thu, 22 Feb 2024 10:30:57 GMT

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

Re: Distinct Count from different rows measure

some_bih — Thu, 22 Feb 2024 11:15:17 GMT

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.

Re: Distinct Count from different rows measure

Zakhamido — Thu, 22 Feb 2024 11:30:31 GMT

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

Re: Distinct Count from different rows measure

some_bih — Thu, 22 Feb 2024 12:00:58 GMT

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.

Re: Distinct Count from different rows measure

Zakhamido — Thu, 22 Feb 2024 13:13:11 GMT

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

Re: Distinct Count from different rows measure

some_bih — Thu, 22 Feb 2024 18:34:59 GMT

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