How to create filters for slicer?

[Mer333]

Hello everyone!

I just recently started to use PP and it works great! But DAX formulas became really a problem for me because mechanics quite different from standard excel.
That's why I really need your help if it won't be too difficult.

I have 2 text columns (actually there are much more but in this task only two will be needed). Structure allows the situation when one unique value in the first column has a few different values in the second column. I mean this will be a few rows, but value in the first column will be the same.

In the current task I have to create a slicer with 4 filters for the fisrt column:
1) If for unique value of 1 column do exist both two values "text1" and "text2" it should be category 1.
2) If for unique value of 1 column does exist value "text1" but no "text2" it should be category 2.
3) If for unique value of 1 column does exist value "text2" but no "text1" it should be category 3.
4) If for unique value of 1 column doesn't exist neither value "text1" nor "text2" it should be category 4.

Which function should I use and how?

 

[scottsen]

I am having a hard time figuring out if you need to setup some relationships, ... or just need a fancy DAX formula.

Are you hoping the results are in a slicer?

 

[Mer333]

Thank you for your reply.
First of all I need a fancy DAX formula, for example, to create an association for this conditions to a new column. Then I could use this column like a filter for a slicer.
Or it could be a new table for relationship with 4 categories which would associate with first table, but it doesn't matter, because firstly I need to understand how to sort out my data to 4 categories and create an association in new column.

 

[scottsen]

I am guessing you want to add a calculated column to a table where these text1 and text2 might exist? Like...

=IF (Table[Col1] = "text1" && Table[Col2] = "text2", 1,
IF (Table[Col2] = "text1" && Table[Col2] <> "text2", 2,
IF (Table[Col2] <> "text" && Table[Col2] = "text2", 3,
4)

?

 

[Mer333]

I am guessing you want to add a calculated column to a table where these text1 and text2 might exist? Like...

The problem is that I have to do it with association to the unique values in the 1 column.

Example:

[TABLE="width: 203"]
<tbody>[TR]
[TD]Fish
[/TD]
[TD]Textn
[/TD]
[TD]Category4
[/TD]
[/TR]
[TR]
[TD]Fish
[/TD]
[TD]Textn
[/TD]
[TD]Category4
[/TD]
[/TR]
[TR]
[TD]Fish
[/TD]
[TD]Textn
[/TD]
[TD]Category4
[/TD]
[/TR]
[TR]
[TD]Sauce
[/TD]
[TD]Text1
[/TD]
[TD]Category2
[/TD]
[/TR]
[TR]
[TD]Meat
[/TD]
[TD]Text2
[/TD]
[TD]Category3
[/TD]
[/TR]
[TR]
[TD]Chicken
[/TD]
[TD]Textn
[/TD]
[TD]Category1
[/TD]
[/TR]
[TR]
[TD]Ham
[/TD]
[TD]Textn
[/TD]
[TD]Category3
[/TD]
[/TR]
[TR]
[TD]Chicken
[/TD]
[TD]Text2
[/TD]
[TD]Category1
[/TD]
[/TR]
[TR]
[TD]Chicken
[/TD]
[TD]Textn
[/TD]
[TD]Category1
[/TD]
[/TR]
[TR]
[TD]Chicken
[/TD]
[TD]Text1
[/TD]
[TD]Category1
[/TD]
[/TR]
[TR]
[TD]Ham
[/TD]
[TD]Textn
[/TD]
[TD]Category4
[/TD]
[/TR]
[TR]
[TD]Ham
[/TD]
[TD]Textn
[/TD]
[TD]Category4
[/TD]
[/TR]
[TR]
[TD]Ham
[/TD]
[TD]Text2
[/TD]
[TD]Category3
[/TD]
[/TR]
[TR]
[TD]Meat
[/TD]
[TD]Text2
[/TD]
[TD]Category3
[/TD]
[/TR]
[TR]
[TD]Meat
[/TD]
[TD]Text2
[/TD]
[TD]Category3
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tianbas]

following solution based on above table (only works with 2+1 text values) and is most likely not the best solution for the problem

you could add a column that changes your text into numbers IF ([Text]="text1",0,IF([Text]="text2",2,1))

add two more columns that give you the min and max of that column for the same unique value in the food column
=calculate(max(Table1[CalculatedColumn1]),filter(Table1,Table1[Food]=earlier(Table1[Food]))) --> shows you a 2 for all food that has a row with text2
=calculate(min(Table1[CalculatedColumn1]),filter(Table1,Table1[Food]=earlier(Table1[Food]))) --> shows you a 0 for all food that has a row with text1

and now your "slicer-column" you can do the normal IF([max_column]=2 && [min_column]=1,"Category 1",.....

 

[Tianbas]

the min and max columns are not really needed as separate columns and could be included in the "slicer column" formula but that makes it with 4 different options a big IF statement that PowerPivot most likely can handle but not my brain when I look at the formula

 

[scottsen]

I am hoping Tianbas's solution helps you. If not, I might need more help understanding the request/question. Or maybe you can share a complete workbook (via dropbox / google drive / whatever)

 

[Mer333]

following solution based on above table (only works with 2+1 text values) and is most likely not the best solution for the problem

you could add a column that changes your text into numbers IF ([Text]="text1",0,IF([Text]="text2",2,1))

add two more columns that give you the min and max of that column for the same unique value in the food column
=calculate(max(Table1[CalculatedColumn1]),filter(Table1,Table1[Food]=earlier(Table1[Food]))) --> shows you a 2 for all food that has a row with text2
=calculate(min(Table1[CalculatedColumn1]),filter(Table1,Table1[Food]=earlier(Table1[Food]))) --> shows you a 0 for all food that has a row with text1

and now your "slicer-column" you can do the normal IF([max_column]=2 && [min_column]=1,"Category 1",.....

Tianbas thanks a lot!!!

I've spent a few hours trying to understand this logic. It is a really massive algorithm which basically needed to simulate excel function SUMPRODUCT and admitedly very well-created.

However it is quite difficult to use. Previously I've tried very hard to manipulate COUNAX formula with hope that it can provide me the same result easier. Unfortunately I didn't succeed.

Scottsen,
Below you can find a link to complete worksheet. I'd appreciate if you know a better solution.

https://www.sendspace.com/file/mk3czt

 

[scottsen]

I feel like there is something I am completely missing, and maybe the use of such a small sample data is throwing me off.

In the example, Chicken is always Category 1, Fish is always 4, Ham is 3, Meat is (also) 3, and Sauce is 2. But it is not clear to me 'why', or how the 'Data' column factors in, at all?

As it stands, I would just add a lookup table per Food, but I think there is more to this problem...?