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

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your response!

      Unfortunately, this still does not give the desired result. Two points:

      1) He now calculates a cumulative. I would like to count the unique number of people who had income in a given month, while they had no income in the previous three months. And that every month again.

      2) He is not looking back 3 months now.

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous 

        custCountNoIncomeLast3Months =
VAR _max =
    CALCULATE ( MAX ( 'Calendar'[Year-Month] ), 'fact' )
VAR _filt =
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Month] <= _max )
VAR _minYRMO =
    MINX (
        TOPN (
            4,
            SUMMARIZE ( _filt, 'Calendar'[Year-Month] ),
            'Calendar'[Year-Month], DESC
        ),
        'Calendar'[Year-Month]
    )
VAR _maxYRMO =
    MINX (
        TOPN (
            2,
            TOPN (
                3,
                SUMMARIZE ( _filt, 'Calendar'[Year-Month] ),
                'Calendar'[Year-Month], DESC
            ),
            'Calendar'[Year-Month], DESC
        ),
        'Calendar'[Year-Month]
    )
VAR _minYRMO1 =
    CALCULATE ( MIN ( 'Calendar'[Date] ), 'Calendar'[Year-Month] = _minYRMO )
VAR _maxYRMO1 =
    CALCULATE ( MAX ( 'Calendar'[Date] ), 'Calendar'[Year-Month] = _maxYRMO )
VAR _except1 =
    EXCEPT (
        VALUES ( 'fact'[ID] ),
        SUMMARIZE (
            FILTER (
                ALL ( 'fact' ),
                'fact'[DATE] >= _minYRMO1
                    && 'fact'[DATE] <= _maxYRMO1
            ),
            'fact'[ID]
        )
    )
RETURN
    COUNTROWS ( _except1 )