Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

FILTER /w string variable

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...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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!