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 firs...
  • 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]
)
Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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]
)