Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Count how many values of one variable are in another variable

Hello everyone. I have this table

(https://docs.google.com/spreadsheets/d/11g-BxXQ-AjcXMVl2h_Yji3bk63BqKxLw/edit?usp=sharing&ouid=106682623530401867716&rtpof=true&sd=true)

 

and I want to create something similar to this

 

The only way I could think of to do this is:

Measure =
var SelectedMonth = selectedvalue(T1[YearMonth])
var AllUserMonth= All users who bought in SelectedMonth , filtered by first_order = True

//Then:
Var Day01= All users who bought on day one (Daydate=1), filtered by SelectedMonth
Var Day02= All users who bought on day two (Daydate=2), filtered by SelectedMonth
Var Day03= All users who bought on day three, filtered by SelectedMonth

etc.... untill

Var Day31= All users who bought on day 31, filtered by SelectedMonth

//So, here at the end, make a Count of  "how many Day01 users there are in AllUserMonth"; "how many Day02 users there are in AllUserMonth"..... etc untill Day31.

 

So, I need a way to calculate "how many values of one variable are in this other variable". I tried several ways with addcolumns but no success. 

I know this is inefficient, that if the SelectMonth doesn't work I'll have to create a variable for each month and then day variables for each month... but I have no problem writing all that (And it's the only way I could think of that could replicate the Excel table in PBI 😣)

 

 

Any help is welcome, thanks 🙂

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    嗨Anonymous，

     

    You can get the required date like this:

    VAR _CUR_DATE = MAX('T1'[ORDER_DATE])
VAR _DATE_MIN = DATE(YEAR(_CUR_DATE),MONTH(_CUR_DATE),DAY(_CUR_DATE))
VAR _DATE_MAX = DATE(YEAR(_CUR_DATE),MONTH(_CUR_DATE),DAY(_CUR_DATE))+1

    Please feel free to let me know if I misunderstood your needs. It would be helpful if you could attach the measure you created for each day below.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data