Joining to a filtered table

[shadowsong42]

I have a Stores ISP fact table that has, among other things, ProductID, SubsidiaryID, SnapshotDate, and Stores Replenish. The first three columns are what you'd expect, Stores Replenish is populated with either a number greater than zero, or a blank.

The Product dim table contains ProductID and SKU. The Subsidiary dim table contains SubsidiaryID and Subsidiary. There are existing relationships between the fact table and the dim tables on ProductID and SubsidiaryID

I would like a table that shows distinct SKU and Subsidiary where the corresponding ProductID and SubsidiaryID exist in Stores ISP with a SnapshotDate within the past year. Then I would like a calculated "Replenishable" column that returns TRUE where that combination appears in Stores ISP in the last 8 days with a Stores Replenish greater than 0, and otherwise returns blank.

Here's my current query.

Replenish = 
SELECTCOLUMNS(
    NATURALLEFTOUTERJOIN(
        NATURALINNERJOIN(
            NATURALINNERJOIN(
                SELECTCOLUMNS(
                    FILTER('Stores ISP', 'Stores ISP'[SnapshotDate] > TODAY() -365),
                    'Stores ISP'[ProductID],'Stores ISP'[SubsidiaryID]
                ),
                'Product'
            ),
            'Subsidiary'
        ),
        FILTER(
                'Stores ISP',
                'Stores ISP'[Stores Replenish] >0 
                    && 'Stores ISP'[SnapshotDate] > TODAY() -8
        )
    ),
    [SKU],[Subsidiary],"Replenishable",NOT(ISBLANK([Stores Replenish]))
)

I'm getting stuck at the very beginning - the error message says "No common join columns detected. The join fuction 'NATURALINNERJOIN' requires at least one common join column."

I'm pretty sure this is due to the SELECTCOLUMNS that I wrapped the filtered Stores ISP table in. I did that in order to eliminate duplicates from the ProductID and SubsidiaryID rows being returned.

However, if I remove that, the query still fails - "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000'".

How do I reduce the number of rows being returned while still being able to join on ProductID and SubsidiaryID?

[shadowsong42]

Partial solution: don't bother joining to product and subsidiary, use summarize instead of select columns, make multiple tables instead of trying to do it all in one step.

It's only partial because I still can't get Power BI to recognize the relationship between Replenishable and the Product/Subsidiary dim tables.

Maybe I need to create relationships to all the various fact tables instead? (There are a lot - Actuals, CFO Forecast, Demand Planning Forecast, DC Inventory, Stores ISP, and Supply are the ones referenced in my matrix - and they all have their own relationships back to Product and Subsidiary.) But maybe the problem is fundamentally related to direct query tables vs local tables and that won't work either.

Table code:

ReplenishOnly = 
SUMMARIZE (
	FILTER (
		'Stores ISP',
		'Stores ISP'[Stores Replenish] > 0
			&& 'Stores ISP'[SnapshotDate] > TODAY () - 8
	),
	'Stores ISP'[ProductID], 
	'Stores ISP'[SubsidiaryID]
)

ReplenishAll = 
SUMMARIZE (
	'Stores ISP', 
	'Stores ISP'[ProductID], 
	'Stores ISP'[SubsidiaryID]
)
	
Replenishable = 
ADDCOLUMNS (
	'ReplenishAll',
	"Replenishable",
	IF (
		NOT ISBLANK (
			LOOKUPVALUE (
				ReplenishOnly[ProductID],
				ReplenishOnly[ProductID], ReplenishAll[ProductID],
				ReplenishOnly[SubsidiaryID], ReplenishAll[SubsidiaryID]
			)
		),
		TRUE,
		FALSE
	)
)

[v-aatheeque]

Hi @shadowsong42 ,

Thank you for the response and providing the solution. We want to kindly follow up to check if the issue is resolved? if yes, kindly mark the helpful answer as a solution if you feel that makes sense. More people in the community will benefit from the thread.

[v-aatheeque]

Hi @shadowsong42 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @shadowsong42 

Have you resolved the issue? If yes, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[shadowsong42 · Accepted solution]

I have discovered that relationships are broken in some way between Direct Query tables and calculated tables, so I am unable to use the calculated table measure as a slicer to filter Direct Query data in the visuals.

While it is possible that converting or duplicating some of the Direct Query tables to Import mode would solve the broken relationship issue, the underlying problem I was trying to solve was to highlight SKU&Subsidiaries incorrectly marked as not replenishable. Since I am having so much trouble with the slicer, I have decided to focus on the more correct solution to this underlying problem, which is to work with the upstream data support teams to fix the source data.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Shravan133]

Try this:

Replenish =
VAR OneYearBack = TODAY() - 365
VAR EightDaysBack = TODAY() - 8

RETURN
ADDCOLUMNS(
SUMMARIZE(
FILTER(
'Stores ISP',
'Stores ISP'[SnapshotDate] > OneYearBack
),
'Product'[SKU],
'Subsidiary'[Subsidiary]
),
"Replenishable",
VAR MatchingRows =
CALCULATE(
COUNTROWS('Stores ISP'),
'Stores ISP'[Stores Replenish] > 0,
'Stores ISP'[SnapshotDate] > EightDaysBack
)
RETURN IF(MatchingRows > 0, TRUE(), BLANK())
)

[shadowsong42]

This does not run. The error "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." is returned. The relevant code snipped appears to be this:

CALCULATE(
	COUNTROWS('Stores ISP'),
	'Stores ISP'[Stores Replenish] > 0,
	'Stores ISP'[SnapshotDate] > TODAY() - 8
)