Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
huzefa_53
Helper I
Helper I

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

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 
huzefa_53_0-1653462319815.png

 

kindly help i am completely stuck here 

 
5 REPLIES 5
huzefa_53
Helper I
Helper I

huzefa_53_0-1653463292972.png

getting this

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.

HI @v-chenwuz-msft  ,

 

huzefa_53_0-1653895785770.png

 

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_)
 
huzefa_53_1-1653895850174.png

 

still getting this error

 

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.

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...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.