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 based on above dataset, what is the expected result. Can you mention that as well?

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

    nandukrishnavsI want a table like this:

     

    I think I can compare the Target and Reached columns after that to count the number of activities lagging behind.

     

    Thanks

    • 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.