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:

iduser_idends_atcreated_at
11 18/12/2022
2222/12/202219/12/2022
33 22/12/2022

 

What I'm trying to obtain is a crossjoin between my dates table and the user_id table, but only for that in which that user was active, or Dates[date] has to be bigger after created_at and before ends_at if ends_at is not blank. If ends_at is blank it needs to go up to today.

What I'm trying to achieve is something like this:

user_iddate
118/12/2022
119/12/2022
120/12/2022
121/12/2022
122/12/2022
123/12/2022
219/12/2022
220/12/2022
221/12/2022
222/12/2022
322/12/2022
323/12/2022

 

I understand that I need to do a crossjoin with filters, but I didn't find any relatable solution so far.

  • 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]
)

4 Replies

  • TheProv's avatar
    TheProv
    Frequent Visitor
    3 years ago

    This looks exactly like what I was trying to achieve! Worked like a charm. Thanks!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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]
)
  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi TheProv 

     

    Apparently Jihwan's solution works. It is also advisible to process the data in Power Query, the code looks like this:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"ends_at", type date}, {"created_at", type date}}, "en-150"),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type with Locale",null,#date(2022, 12, 23),Replacer.ReplaceValue,{"ends_at", "created_at"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"id"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Custom", each [ends_at]-[created_at]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ends_at"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns", "Custom.1", each [Custom]+1),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom4",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns2", "Custom", each {0..[Custom.1]-1}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded Custom",{"Custom.1"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns3", "Custom.1", each Date.AddDays([created_at],[Custom])),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom2",{"created_at", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns4",{{"Custom.1", "date"}})
in
    #"Renamed Columns"

     

    it lands the same result:

  • AlB's avatar
    AlB
    Community 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.