Forum Discussion

Zakhamido's avatar
Zakhamido
Frequent Visitor
2 years ago
Solved

Matching Values from 2 tables

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

 

 

 

 

  • Fowmy's avatar
    Fowmy
    2 years ago

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

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Zakhamido 

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


    • Zakhamido's avatar
      Zakhamido
      Frequent Visitor
      2 years ago
      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
                  )
              ),
              0
          ),
          0
      )
      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

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