Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX

I need the following in DAX format.

 

Requirement: I have a table called Transactions which has 3 columns ID, [Billing Type] and [Year-Month] as mentioned below. I need to create a measure that should show that ID 1 receives its 10th Credit Card in the month of October, while ID 2 receives its 10th Credit Card in the month of May, while ID 3 receives its 10th Credit Card in the month of October. 

 

So we have now 1 in May (2017-05) and 2 in Oct (2017-10)

 

My expectation is that this measure would show 0 from 2017-01 through 2017-04, 1 from 2017-05 through 2017-09, and 3 from 2017-10 thru 2017-12.

 

Transactions Table

IDBilling TypeYear-Month
1Credit Card2017-01
1Credit Card2017-02
1Credit Card2017-03
1Credit Card2017-04
1Credit Card2017-05
1Credit Card2017-06
1Credit Card2017-07
1Credit Card2017-08
1Credit Card2017-09
1Credit Card2017-10
1Credit Card2017-12
2Credit Card2017-01
2Credit Card2017-02
2Credit Card2017-02
2Credit Card2017-03
2Credit Card2017-03
2Credit Card2017-04
2Credit Card2017-04
2Credit Card2017-05
2Credit Card2017-05
2Credit Card2017-06
2Credit Card2017-12
3Credit Card2017-01
3Credit Card2017-06
3Credit Card2017-01
3Credit Card2017-01
3Credit Card2017-12
3Credit Card2017-01
3Credit Card2017-06
3Credit Card2017-12
3Credit Card2017-01
3Credit Card2017-10
5Credit Card2017-10
5Credit Card2017-12
   


I need to show the output as below

 

Now I need to get the same result using DAX. Can someone please help me to get the same using DAX?

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Please change the MEASURE to following

     

    But i Have following observtions

    1) 10 th occurence of ID 2 is in June

    2) November 2017 date doesn't exist in your data

     

     

    Measure =
VAR mycount =
    CALCULATE (
        COUNT ( TableName[ID] ),
        FILTER (
            ALL ( TableName ),
            TableName[Year-Month] <= SELECTEDVALUE ( TableName[Year-Month] )
                && TableName[RANK] = 10
        )
    )
RETURN
    IF ( ISBLANK ( mycount ), 0, mycount )

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    Try this technique

     

    1) First Add an Index Column from the Query Editor

     

    2) Then Add a RANK Calculated Colum

    RANK =
RANKX (
    FILTER ( TableName, TableName[ID] = EARLIER ( TableName[ID] ) ),
    TableName[Index],
    ,
    ASC,
    DENSE
)

    3) Now you can use this MEASURE

     

    Measure =
CALCULATE ( COUNT ( TableName[ID] ), TableName[RANK] = 10 )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Sorry Zubair_Muhammad, The result is not as expected 

       

      Until April (2017-05) there is no 10th Credit Card. We have 1 in May (2017-05) and then 2 in October (2017-10). So we have to plot 0 till April and 1 till September and since we have 2 in October, we have to sum up the May 1 and October 2 and show as 3 from October to December

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Anonymous

         

        Please change the MEASURE to following

         

        But i Have following observtions

        1) 10 th occurence of ID 2 is in June

        2) November 2017 date doesn't exist in your data

         

         

        Measure =
VAR mycount =
    CALCULATE (
        COUNT ( TableName[ID] ),
        FILTER (
            ALL ( TableName ),
            TableName[Year-Month] <= SELECTEDVALUE ( TableName[Year-Month] )
                && TableName[RANK] = 10
        )
    )
RETURN
    IF ( ISBLANK ( mycount ), 0, mycount )