FILTER /w string variable

[Anonymous]

Hi all,

I'm facing a puzzle which i haven't been able to solve / google for 2 days now even though I feel it should be easy to accomplish. 

Quick explanation: im trying to train my DAX skills by creating a calculator for a computergame (Anno 1800), so please forgive the stupidness of the data in the tables :). The game is based on production chains. For instance: Rum is made out of wood and sugar cane. What i want to accomplish is that when I use a slicer to select "Rum", i get a table which shows me not only the row for rum, but also wood and sugar cane. 

Im at the point where i get the desired table when hardcoding the items, but of course i prefer to use a variable, as i dont want to hardcode 100+ items. Here's where things get tricky: The FILTER doesn't seem to accept any variables. I cant call them out directly, or by first "pulling them in" with a VAR. The [ING1] & [ING2] are textstrings based on a LOOKUPVALUE. I've added screenshots for reference.

So. In short. The variables are calculated correctly when added to a card, for example. But the same variables are not being used in my filter context as such. Is what i want even possible? What am I missing?

Thanks for your help in advance!

Ramon

create variable (textstring)

Desired result, hardcodedTrying variables in FILTER, no result

[Anonymous]

Hi Whitewater, 

thanks for your reply. I'm afraid it's a bit more complicated than that. If it was component(s) into item it would be fairly easy, despite a lot of manual labor, to create a table such as the first one you're suggesting. Unfortunately, there's a three-layer system ánd there is the ability to overwrite one of the ingredients for any other.

The three-layer setup means that the base table would be extremly vast, and I dont feel like writing it for hundreds of entries. The overwriting of any ingredient into any other makes it completely impossible. 

Thats the reason why i'm looking to use references for the filter context, but unfortunately that just doesn't seem to work. 

Does anyone know whether referencing to variable string texts in filter context is actually technically possible? If so, how?

I've linked the .pbix (dont mind the random tables created - i've just been trying things) to give you all a better idea of the base table

Thanks in advance!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

As far as I know Power BI doesn't support us to create calcualted table to show dynamic results. Here I have a workaround. I suggest you to create a 'Goods' table with all items and ingredients in 'Product' table.

Item and Ingredient = 
VAR _UNION =
    UNION (
        VALUES ( Products[Item] ),
        VALUES ( Products[Ingredient 1] ),
        VALUES ( Products[Alternative ingr 2] ),
        VALUES ( Products[Alternative ingr 3] )
    )
VAR _FILTER =
    SUMMARIZE ( FILTER ( _UNION, [Item] <> BLANK () ), [Item] )
RETURN
    _FILTER

Then create a measure to filter this table.

Filter = 
VAR _ITEM =
    SELECTEDVALUE ( Products[Item] )
VAR _Ingredient1 =
    SELECTEDVALUE ( Products[Ingredient 1] )
VAR _Ingredient2 =
    SELECTEDVALUE ( Products[Ingredient 2] )
VAR _Ingredient3 =
    SELECTEDVALUE ( Products[Ingredient 3] )
VAR _CURRENTVALUE =
    MAX ( 'Item and Ingredient'[Goods] )
RETURN
    IF (
        _CURRENTVALUE = _ITEM
            || _CURRENTVALUE = _Ingredient1
            || _CURRENTVALUE = _Ingredient2
            || _CURRENTVALUE = _Ingredient3,
        1,
        0
    )

Add this measure into visual level filter and set it to show items when value = 1.

Result is as below. When I select Rum in slicer, we can see Rum/Wood and Suger cane in table.

Best Regards,
Rico Zhou

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

[Anonymous]

Hi Rico,

thanks again for the help. Setting up a shadowtable was the obvious choice. I didnt even have to create one with join, as all ingredients and alternative ingredients are represented in the original ITEM list. Just a VALUES(item) as shadowtable, and then filtering as you suggested, did the trick perfectly.

Thanks for the help!

[Whitewater100]

Hi Ramon:

Do you have a way to set up some tables to help with your question? I'll paste below one way to lay out the model foundation to make the job easier:

Dim_Components
Prduct_ID	Product	Component	Share	Cost(Optional)
1	Rum	Wood	0.1
1	Rum	Sugar	0.4
1	Rum	Water	0.5
2	Bread	Wheat	0.25
2	Bread	Flour	0.25
2	Bread	Yeast	0.25
2	Bread	Milk	0.25
3	Other	A	0.33
3	Other	B	0.33
3	Other	C	0.33
Dim_Products
Prod_ID	Name	Sell_Price
1	Rum	20
2	Bread	3
3	Other	10
Fact Sales
Cust_ID	Prod_ID	Trans Date	Qty
other potential Dim Tables - "Dates" & "Customers"