Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
HI,
kindly help i am completely stuck here
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 .
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 ,
i have formatted index to Whole Number
and i have also change dax like
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.
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |