A fact table getting info from dimension table and fact table then grouping & distinct count

[jajaAtPowerbi]

Hi , 

My scenario is 2 fact tables (point & trans) connecting to a dimension table (profile)

point table is filtered by calendar table (e.g. 3-Aug-2023 to 4-Aug-2023), there are 124 members with this date selection

a. getting the regist date of these 124 members from profile table

b. getting the last order date from trans for these 124 members from trans table

1. when regist date of member  >= 3-Aug-2023, would call this member group as Group A and get the distinct count of the members

2. when regist date of member  < 3-Aug-2023 and last order date >= 3-Aug-2022, would call this membes group as Group B and get the distinct count of the members 

3. when regist date of member  < 3-Aug-2023 and last order date < 3-Aug-2022, would call this member group as Group C and get the distinct count of the members 

4. when regist date of member  < 3-Aug-2023 and last order date is null, would call this member group as Group D and get the distinct count of the members

have written 2 measures [_Last_Order_Date], [_Last Order Date (for points member)] about this, but not sure how to proceed for [_Historical Purchase Group Count]

have uploaded pbix file to onedrive , could anybody help to get this work?

fact-bridge-fact.pbix

[jajaAtPowerbi]

could anybody help on this?

[jajaAtPowerbi]

the result will look like this when date range 3-Aug-2023 to 4-Aug-2023 is selected

[Mahesh0016]

@jajaAtPowerbi Please Can You Share Your Expected Output. Thank You.

[jajaAtPowerbi]

the result will looks like this when date range 3-Aug-2023 to 4-Aug-2023 is selected

[Mahesh0016]

@jajaAtPowerbi  Please Can you elaborate Row labels Column. Because in measure you use 1 ,2,3 so on Lable and in picture A,B,C and so on.

[jajaAtPowerbi]

Sorry for the confusion, i have reuploaded the pbix file and the label of each group is A,B,C,D, I never use 1,2,3 so on

[Mahesh0016]

@jajaAtPowerbi 

var label = SELECTEDVALUE('Grouping_Historical_Purchase'[Label]) you use this var in measure
but 'Grouping_Historical_Purchase'[Label] in this column have you any A, B, C and D value?

[jajaAtPowerbi]

@Mahesh0016 sorry again, this is my bad, i have updated on the pbix file

[Mahesh0016]

@jajaAtPowerbi  I hope this helps you. Thank You.

_LastOrderDate (for points member) =

CALCULATE

    (

        [_Last Order Date]

        //,CROSSFILTER(Profile[member_id],point[member_id],Both)

    )

_Historical Purchase Group Count =

var start_date = [Start Date]

var start_date_last_year = [Start Date LY]

var label = SELECTEDVALUE('Grouping_Historical_Purchase'[Label])

var last_order_date = [_LastOrderDate (for points member)]

return

SWITCH(

    TRUE(),

----------------------------------------------------------------------------------------------------------------------------------

    label = "A", 

    CALCULATE

        (

            [Redeemed Member Count (Member Profile)],



            TREATAS(VALUES(point[member_id]),Profile[member_id]),

            Profile[regist_date] >= start_date

        ),

----------------------------------------------------------------------------------------------------------------------------------

    label = "B", 

    CALCULATE

        (

            [Redeemed Member Count (Member Profile)],

            TREATAS(VALUES(point[member_id]),Profile[member_id]),

            Profile[regist_date] < start_date,

            FILTER(ALLSELECTED('calendar'),last_order_date >= start_date_last_year)

        ),

----------------------------------------------------------------------------------------------------------------------------------

    label = "C", 

    CALCULATE

        (

            [Redeemed Member Count (Member Profile)],

            TREATAS(VALUES(point[member_id]),Profile[member_id]),

            Profile[regist_date] < start_date,

            FILTER(ALLSELECTED('calendar'),last_order_date < start_date_last_year)

        ),

----------------------------------------------------------------------------------------------------------------------------------

    label = "D", 

    CALCULATE

        (

            [Redeemed Member Count (Member Profile)],

            TREATAS(VALUES(point[member_id]),Profile[member_id]),

            Profile[regist_date] < start_date,

            FILTER(ALLSELECTED(Trans),ISBLANK(last_order_date))

        ),

----------------------------------------------------------------------------------------------------------------------------------

    BLANK()

)

[jajaAtPowerbi]

Thanks for your effort but this is not correct...since calendar table has no relaptionship with last_order_date , last_order_date is devired from trans table by getting the max order date for each member id and order date is ealier than the start date from the date slicer

[Mahesh0016]

@jajaAtPowerbi Here is what it is I get a result.

_Historical Purchase Group Count = 

var start_date = [Start Date]

var start_date_last_year = [Start Date LY]

var label = SELECTEDVALUE('Grouping_Historical_Purchase'[Label])

var last_order_date = [_Last Order Date (for points member)]

return

SWITCH(

    TRUE(),

----------------------------------------------------------------------------------------------------------------------------------

    label = "Existing Non-Buyer"

    ,CALCULATE

        (

            [Redeemed Member Count (Member Profile)],

            CROSSFILTER

            (

                Profile[member_id], 

                POINT[member_id],

                Both

            ),

            Profile[regist_date] >= start_date

        ),

----------------------------------------------------------------------------------------------------------------------------------

    label = "R12M Buying"

    ,CALCULATE

        (

            [Redeemed Member Count (Member Profile)],

            CROSSFILTER

            (

                Profile[member_id], 

                POINT[member_id],

                Both

            ),

            Profile[regist_date] < start_date,

            FILTER('calendar',last_order_date >= start_date_last_year)

        ),

----------------------------------------------------------------------------------------------------------------------------------

    label = ">12M Buyer"

    ,CALCULATE

        (

            [Redeemed Member Count (Member Profile)],

            CROSSFILTER

            (

                Profile[member_id], 

                POINT[member_id],

                Both

            ),

            Profile[regist_date] < start_date,

            FILTER('calendar',last_order_date < start_date_last_year)

        ),

----------------------------------------------------------------------------------------------------------------------------------

    label = "Recent Acquistion"

    ,CALCULATE

        (

            [Redeemed Member Count (Member Profile)],

            CROSSFILTER

            (

                Profile[member_id], 

                POINT[member_id],

                Both

            ),

            Profile[regist_date] < start_date

        ),

----------------------------------------------------------------------------------------------------------------------------------

    BLANK()

)