Forum Discussion

SahityaYeruband's avatar
1 year ago
Solved

How to use a dataset Column without aggregation in a calculate function

Hi All, I have 2 datasets -  Logs: Path Hits Month_Num Master File: Country Dept Path   Relationship -  MasterFile(Path) to Logs(path)  1:M relationship I have...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,SahityaYeruband .Thank you for your reply.
    Like this?


    select Month=1

    select Month =2

    In order to exactly fit your given case data, I have modified the values of some Hits in the original data appropriately
    This is the latest test data:

    Path

    Hits

    Month_Num

    Sum_eachMonthHits

    Sum_eachHits02

    Index

    A

    5

    1

    6

    9

    1

    A

    1

    1

    6

    9

    2

    B

    12

    1

    20

    26

    3

    B

    8

    1

    20

    26

    4

    C

    1

    1

    2

    18

    5

    C

    1

    1

    2

    18

    6

    A

    3

    2

    3

    9

    7

    B

    6

    2

    6

    26

    8

    C

    10

    2

    16

    18

    9

    C

    6

    2

    16

    18

    10

     

    Country

    Dept

    Path

    US

    Sales

    A

    US

    HR

    B

    CN

    IT

    C

    I'm still using the two calculated columns I created, and I'm using their data to write a measure.
    Here are the measures I created:

    M_All available values =
    VAR _count =
        CALCULATE (
            DISTINCTCOUNT ( 'Logs'[Sum_eachHits02] ),
            FILTER ( 'Logs', 'Logs'[Sum_eachHits02] > 0 && 'Logs'[Sum_eachHits02] < 10 )
        )
    RETURN
        IF ( _count = BLANK (), 0, _count )
    
    
    
    hit_10 = 
     CALCULATE( MAX('Logs'[Sum_eachMonthHits]),'Logs'[Sum_eachMonthHits]<10)
    Month_reportCount =
    VAR _countValues =
        CALCULATE (
            DISTINCTCOUNT ( 'Logs'[Sum_eachMonthHits] ),
            'Logs'[Sum_eachMonthHits] < 10
                && 'Logs'[Sum_eachMonthHits] > 0
                && 'Logs'[Sum_eachMonthHits] <> BLANK ()
        )
    RETURN
        IF ( _countValues = BLANK (), 0, _countValues )

    The reason for using IF judgement is to make the rows that originally had no value display as 0 instead of the original blank. in this case, since the relationship created will have the effect of field filtering, the system will ignore the rows that originally had no value to display by default. I use IF to determine if the current row's measure result is empty, and if it is empty, I assign a value of 0 to it.

    I hope my test results can give you help.


    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.