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