Forum Discussion

enoch99's avatar
enoch99
Icon for Helper I rankHelper I
3 years ago
Solved

Distinct Count by comparing two summarized tables

Hi everyone,   I am new to DAX and need your support in solving this problem.    I have two Excel files. One contains yearly target planned for different activities while the second contains mont...
  • nandukrishnavs's avatar
    nandukrishnavs
    3 years ago

    enoch99 You can create a calculated column in Target table

     

     

    Reached =
SUMX (
    FILTER ( Reached, [Activity] = EARLIEST ( Target[Activity] ) ),
    [Reached]
)

     

    If you need a measure, then try the below measure

    Lagging = 
VAR _target =
    ADDCOLUMNS (
        Target,
        "@Reached",
            SUMX (
                FILTER ( Reached, [Activity] = EARLIER ( Target[Activity] ) ),
                [Reached]
            )
    )
VAR _result =
    COUNTX ( FILTER ( _target, [@Reached] < [Target] ), [Activity] )
RETURN
    _result
nandukrishnavs's avatar
nandukrishnavs
Icon for Community Champion rankCommunity Champion
3 years ago

enoch99 You can create a calculated column in Target table

 

 

Reached =
SUMX (
    FILTER ( Reached, [Activity] = EARLIEST ( Target[Activity] ) ),
    [Reached]
)

 

If you need a measure, then try the below measure

Lagging = 
VAR _target =
    ADDCOLUMNS (
        Target,
        "@Reached",
            SUMX (
                FILTER ( Reached, [Activity] = EARLIER ( Target[Activity] ) ),
                [Reached]
            )
    )
VAR _result =
    COUNTX ( FILTER ( _target, [@Reached] < [Target] ), [Activity] )
RETURN
    _result
enoch99's avatar
enoch99
Icon for Helper I rankHelper I
3 years ago

nandukrishnavs  Thank you for your quick response. This is what I was looking for.