How to make dynamic join on dax between var table and existing table

[huzefa_53]

HI, 

dining frequency =

var filtered = CALCULATETABLE('POS Checks',FILTER('POS Checks', 'POS Checks'[check_status] == "Posted" || 'POS Checks'[check_status] == "Closed" || 'POS Checks'[check_status] == "Reversed"),FILTER('POS Checks', 'POS Checks'[check_date] >= MINA('Calendar'[Date]) &&'POS Checks'[check_date] <= [Max Selected Date]),FILTER('POS Checks','POS Checks'[check_module] in {"Dining","F&B POS"}))

VAR virtualTable = GROUPBY(filtered, 'POS Checks'[member_id],'POS Checks'[check_date], "Frequency", COUNTAX(CURRENTGROUP(),'POS Checks'[check_date]))

VAR newvirtualTable = GROUPBY(virtualTable, 'POS Checks'[member_id], "Frequency", COUNTAX(CURRENTGROUP(),[Frequency]))

VAR countAsGroup = FILTER(newvirtualTable, COUNTROWS (FILTER ('MM Member Dining Frequency', [Frequency] >= 'MM Member Dining Frequency'[Min] && 'MM Member Dining Frequency'[Max])))

/*var count_ = CALCULATE(COUNT('POS Checks'[member_id]),

FILTER (

DISTINCT (newvirtualTable),

COUNTROWS (

FILTER (

'MM Member Dining Frequency',

AND([Frequency] >= 'MM Member Dining Frequency'[Min], [Frequency] <= 'MM Member Dining Frequency'[Max])

)

)

)

)*/

var count_ = COUNTAX(CROSSJOIN(ADDCOLUMNS(newvirtualTable,"Index",IF (

[Frequency] = 0,

1,

IF (

[Frequency] >= 1

&& [Frequency] <= 2,

2,

IF (

[Frequency] >= 3

&& [Frequency] <= 5,

3,

IF (

[Frequency] >= 6

&& [Frequency] <= 10,

4,

IF (

[Frequency] >= 11

&& [Frequency] <= 20,

5,

IF (

[Frequency] >= 21

&& [Frequency] <= 50,

6,

IF ( [Frequency] > 50, 7, 7 )

)

)

)

)

)

)),'MM Member Dining Frequency'),[Index])

return IF(ISBLANK(count_), 0, count_)

currently i have this dax i have tested in table dax newvirtualTable  is working fine with all filter value now it has frequency against each member between date range the only problem is to assign that frequency number a index and make dynamic relation on that index with my frequency table 

kindly help i am completely stuck here 

[huzefa_53]

getting this

[v-chenwuz-msft]

Hi @huzefa_53 ,

You should make sure the type of the Index of both table are the same, all number or all text. The Index in your table MM Member Dining Frequency is text, but the Index column in your count_ is int. 

Please refer the remark in DAX function NATURALINNERJOIN .

• olumns being joined on must have the same data type in both tables.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[huzefa_53]

HI @v-chenwuz-msft  ,

i have formatted index to Whole Number

and i have also change dax like 

dining frequency =

var filtered = CALCULATETABLE('POS Checks',FILTER('POS Checks', 'POS Checks'[check_status] == "Posted" || 'POS Checks'[check_status] == "Closed" || 'POS Checks'[check_status] == "Reversed"),FILTER('POS Checks', 'POS Checks'[check_date] >= MINA('Calendar'[Date]) &&'POS Checks'[check_date] <= [Max Selected Date]),FILTER('POS Checks','POS Checks'[check_module] in {"Dining","F&B POS"}))

VAR virtualTable = GROUPBY(filtered, 'POS Checks'[member_id],'POS Checks'[check_date], "Frequency", COUNTAX(CURRENTGROUP(),'POS Checks'[check_date]))

VAR newvirtualTable = GROUPBY(virtualTable, 'POS Checks'[member_id], "Frequency", COUNTAX(CURRENTGROUP(),[Frequency]))

VAR countAsGroup = FILTER(newvirtualTable, COUNTROWS (FILTER ('MM Member Dining Frequency', [Frequency] >= 'MM Member Dining Frequency'[Min] && 'MM Member Dining Frequency'[Max])))

/*var count_ = CALCULATE(COUNT('POS Checks'[member_id]),

FILTER (

DISTINCT (newvirtualTable),

COUNTROWS (

FILTER (

'MM Member Dining Frequency',

AND([Frequency] >= 'MM Member Dining Frequency'[Min], [Frequency] <= 'MM Member Dining Frequency'[Max])

)

)

)

)*/

var count_ = CALCULATE( COUNT('MM Members'[member_id]),NATURALINNERJOIN(ADDCOLUMNS(newvirtualTable,"Index",FORMAT(IF (

[Frequency] = 0,

1,

IF (

[Frequency] >= 1

&& [Frequency] <= 2,

2,

IF (

[Frequency] >= 3

&& [Frequency] <= 5,

3,

IF (

[Frequency] >= 6

&& [Frequency] <= 10,

4,

IF (

[Frequency] >= 11

&& [Frequency] <= 20,

5,

IF (

[Frequency] >= 21

&& [Frequency] <= 50,

6,

IF ( [Frequency] > 50, 7, 7 )

)

)

)

)

)

),"General Number")),'MM Member Dining Frequency'))

return IF(ISBLANK(count_), 0, count_)

still getting this error

[v-chenwuz-msft]

Hi @huzefa_53 ,

To use this function NATURALINNERJOIN(),

• Only columns from the same source table (have the same lineage) are joined on. For example, Products[ProductID], WebSales[ProductdID], StoreSales[ProductdID] with many-to-one relationships between WebSales and StoreSales and the Products table based on the ProductID column, WebSales and StoreSales tables are joined on [ProductID].

So it can not use this function in this case. Please use CROSSJOIN() and FILTER() to keep the rows is right.

dining frequency =
VAR filtered =
    CALCULATETABLE (
        'POS Checks',
        FILTER (
            'POS Checks',
            'POS Checks'[check_status] == "Posted"
                || 'POS Checks'[check_status] == "Closed"
                || 'POS Checks'[check_status] == "Reversed"
        ),
        FILTER (
            'POS Checks',
            'POS Checks'[check_date] >= MINA ( 'Calendar'[Date] )
                && 'POS Checks'[check_date] <= [Max Selected Date]
        ),
        FILTER ( 'POS Checks', 'POS Checks'[check_module] IN { "Dining", "F&B POS" } )
    )
VAR virtualTable =
    GROUPBY (
        filtered,
        'POS Checks'[member_id],
        'POS Checks'[check_date],
        "Frequency", COUNTAX ( CURRENTGROUP (), 'POS Checks'[check_date] )
    )
VAR newvirtualTable =
    GROUPBY (
        virtualTable,
        'POS Checks'[member_id],
        "Frequency", COUNTAX ( CURRENTGROUP (), [Frequency] )
    )
VAR countAsGroup =
    FILTER (
        newvirtualTable,
        COUNTROWS (
            FILTER (
                'MM Member Dining Frequency',
                [Frequency] >= 'MM Member Dining Frequency'[Min]
                    && 'MM Member Dining Frequency'[Max]
            )
        )
    ) /*var count_ = CALCULATE(COUNT('POS Checks'[member_id]),
FILTER (
DISTINCT (newvirtualTable),
COUNTROWS (
FILTER (
'MM Member Dining Frequency',
AND([Frequency] >= 'MM Member Dining Frequency'[Min], [Frequency] <= 'MM Member Dining Frequency'[Max])))))*/
VAR count_ =
    COUNTAX (
        FILTER (
            CROSSJOIN (
                ADDCOLUMNS (
                    newvirtualTable,
                    "_Index",
                        SWITCH (
                            TRUE (),
                            [Frequency] = 0, 1,
                            [Frequency] >= 1
                                && [Frequency] <= 2, 2,
                            [Frequency] >= 3
                                && [Frequency] <= 5, 3,
                            [Frequency] >= 6
                                && [Frequency] <= 10, 4,
                            [Frequency] >= 11
                                && [Frequency] <= 20, 5,
                            [Frequency] >= 21
                                && [Frequency] <= 50, 6,
                            7
                        )
                ),
                'MM Member Dining Frequency'
            ),
            [_Index] = [Index]
        ),
        [Index]
    )
RETURN
    IF ( ISBLANK ( count_ ), 0, count_ )

If this does not work, please share your pbix file without sensitive data.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[huzefa_53]

Hi @v-chenwuz-msft 

Thanks for your answer but this still didn't get my desired result my pbix file is attach on this tikcet you can get more information as well that what am i trying to achieve 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Need-to-calculate-frequency-based-on-slicer-v...