Forum Discussion

TheProv's avatar
TheProv
Frequent Visitor
3 years ago
Solved

Crossjoin with filters

Hi everyone. So, I have a table that defines subscriptions periods for users, that is like that: id user_id ends_at created_at 1 1   18/12/2022 2 2 22/12/2022 19/12/2022 3 ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

     

    New table = 
SUMMARIZE (
    GENERATE (
        Data,
        FILTER (
            'Calendar',
            'Calendar'[Date] >= Data[created_at]
                && 'Calendar'[Date]
                    <= IF ( Data[ends_at] <> BLANK (), Data[ends_at], TODAY () )
        )
    ),
    Data[user_id],
    'Calendar'[Date]
)
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
3 years ago

Hi TheProv 

I wold do this in PQ. Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiQwt9QyN9IwMjI6VYnWglI6CQkRFcCChviSJvDNGFpCQ2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user_id = _t, ends_at = _t, created_at = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"user_id", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"ends_at", type date}, {"created_at", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Date", each let 
last_= if [ends_at] = null then Date.From(DateTime.LocalNow()) else [ends_at],
len_ = Duration.Days(last_ - [created_at]) + 1,
res_ =
List.Dates([created_at], len_, #duration(1, 0, 0, 0))
in 
res_),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date")
in
    #"Expanded Date"

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.