Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Count under condition

Hey everyone,

I have a DAX question for you. I have a data set that contains settled income of people on benefits. This income is recorded monthly by date.

To see which people have received new income, I would like to make a chart in which you can see per month for how many people (unique) income has been settled. PLEASE NOTE that no income has been settled in the past 3 months.

 

My dataset contains 3 columns:

 

1) ID (unique personal number).

2) DATE (Start date of settled earnings).

3) KIND (Type of income).

 

I already have a calendar table, so I think I could achieve this with a DAX calculation.

Who can and wants to help me? My experience with DAX is limited.

condition
COUNT
DAX
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous,

    Perhaps you can try to use the following calculated column formula to check the records based on client group and date range conditions to remark the suitable records:

    InCome? = 
VAR result =
    COUNTROWS (
        FILTER (
            'T2',
            VAR currClient =
                EARLIER ( T2[Client] )
            VAR currDate =
                EARLIER ( T2[Date] )
            RETURN
                [Client] = currClient
                    && [Date]
                        > DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
                    && [Date] < currDate
        )
    )
RETURN
    IF ( result > 0, "No", "Yes" )

    Regards,

    Xiaoxin Sheng

8 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  provide sample data and expected output / sample pbix and expected output?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, the link below will take you to 2 files. An Excel with data and a .pbix.

      I have already loaded the data into the .pbix. In addition, I made a graph with the value of a unique count on ID (each unique ID stands for a person) and on the Axis the DATE (this is a date on which someone's income has been settled).

      Now what I want is to add a calculated column to the table. This column should state whether the person (ID) has already had income in the past 3 months prior to the DATE. If yes then 0 and if no then 1.

      In this way I can create a chart that only includes the persons (ID) who had income in a month, if they had no income in the three months prior to that date.

       

      I hope you understand what I mean.

       

      https://8ktd365-my.sharepoint.com/:f:/g/personal/j_brockhus_8ktd365_nl/EpJKNjHxE6BOokW9SiSZSMgBe7Bg8Mz_X46XZc9iS1PXww?e=VuisIE

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        Anonymous  you can use a measure like this which would give you this

        UniqueIDCount/MO =
VAR _count1 =
    DISTINCTCOUNT ( 'fact'[ID] )
VAR _rank =
    RANKX (
        ALLSELECTED ( 'Calendar'[Year-Month] ),
        CALCULATE ( MAX ( 'Calendar'[Year-Month] ) ),
        ,
        ASC,
        DENSE
    )
VAR _mxMO =
    CALCULATE ( MAX ( 'Calendar'[Date] ), 'fact' )
VAR _count =
    EXCEPT (
        SUMMARIZE ( FILTER ( ALL ( 'fact' ), 'fact'[DATE] <= _mxMO ), 'fact'[ID] ),
        VALUES ( 'fact'[ID] )
    )
VAR _count2 =
    COUNTROWS ( _count )
VAR _x =
    IF ( _rank = 1, _count1, _count2 )
RETURN
    _x