topic Re: How to make dynamic join on dax between var table and existing table in DAX Commands and Tips

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

huzefa_53 — Wed, 25 May 2022 07:05:56 GMT

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,

IF (

[Frequency] >= 1

&& [Frequency] <= 2,

IF (

[Frequency] >= 3

&& [Frequency] <= 5,

IF (

[Frequency] >= 6

&& [Frequency] <= 10,

IF (

[Frequency] >= 11

&& [Frequency] <= 20,

IF (

[Frequency] >= 21

&& [Frequency] <= 50,

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

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

huzefa_53 — Wed, 25 May 2022 07:21:41 GMT

getting this

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

v-chenwuz-msft — Mon, 30 May 2022 03:08:58 GMT

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.

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

huzefa_53 — Mon, 30 May 2022 07:31:06 GMT

HI v-chenwuz-msft ,

i have formatted index to Whole Number

and i have also change dax like

dining frequency =

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,

IF (

[Frequency] >= 1

&& [Frequency] <= 2,

IF (

[Frequency] >= 3

&& [Frequency] <= 5,

IF (

[Frequency] >= 6

&& [Frequency] <= 10,

IF (

[Frequency] >= 11

&& [Frequency] <= 20,

IF (

[Frequency] >= 21

&& [Frequency] <= 50,

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

)

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

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

still getting this error

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

v-chenwuz-msft — Wed, 01 Jun 2022 02:49:54 GMT

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.

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

huzefa_53 — Fri, 03 Jun 2022 08:49:38 GMT

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-value-but-need-to/m-p/2523273#M70460