topic Re: Matching Values from 2 tables in DAX Commands and Tips

Matching Values from 2 tables

Zakhamido — Wed, 24 Jan 2024 07:00:34 GMT

I have a problem and I am seeking for help, I have an issue with a DAX formula.

First of all let me explain my requirment, 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 my issue is I am unable to find a way to do it, my current formula (Below) will do it perfectly when I select a record from the table but will not count automatically for records, I think because I am using SelectedValue function so is there any way to do my requirment ?

Just to note that my common column in the 2 tables is substation number

Re: Matching Values from 2 tables

Fowmy — Wed, 24 Jan 2024 08:00:20 GMT

Zakhamido

Could you paste the DAX measure in the reply rather than the image so I can modify it for you?

Re: Matching Values from 2 tables

Zakhamido — Wed, 24 Jan 2024 08:35:35 GMT

MatchedAccounts =

VAR CurrentSubName = SELECTEDVALUE(TIBCOINCIDENT_UPDATED[SUB_NAME])

VAR CurrentStatus = SELECTEDVALUE(TIBCOINCIDENT_UPDATED[STATUS])

VAR CurrentOutageTime = SELECTEDVALUE(TIBCOINCIDENT_UPDATED[Outage_Time_UAE])

VAR CurrentTime = NOW()

RETURN

IF (

CurrentStatus = "Un-Planned Pending" && CurrentOutageTime > CurrentTime - 24,

COALESCE(

CALCULATE (

COUNTROWS(

DISTINCT(

'Medical Case'[Per Src Id]

)

FILTER (

'Medical Case',

'Medical Case'[Substation] = CurrentSubName

)

Re: Matching Values from 2 tables

Fowmy — Wed, 24 Jan 2024 11:49:14 GMT

Zakhamido

Please try this meaure:

MatchedAccounts = 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" && CurrentOutageTime > CurrentTime - 24, CALCULATE ( DISTINCTCOUNT ( 'Medical Case'[Per Src Id] ), 'Medical Case'[Substation] = CurrentSubName ) ) + 0 ), [__DCount] )

Re: Matching Values from 2 tables

Zakhamido — Thu, 25 Jan 2024 06:54:18 GMT

Thanks a lot