Return a list and count of related items

[dsdesrosiers]

Greetings all -

I am trying to solve what seems like a common question but no luck. Perhaps I am just googling poorly but in any case, I am ready to try this forum which has always worked well for me. I want to find phrases related to a selected phrase out of a table. In my case, these are product features for my company but I cannot use those terms here so I am using food terms. 

I have data similar to the below in a table... I also have a dimention table with a list of unique values found on the Phrase column... (not shown but you get the idea I am sure). The tables are related One-to-Many

ID	Phrase
1	avocado
1	peanut
2	avocado
3	chocolate
3	eggs
3	milk
3	peanut
4	bread
4	chocolate
4	eggs
4	peanut

What I would like is a slicer on the Unique list ...and when a value is selected for these results to show a list of related phrases and counts of those phrases. For example, if I were to select Peanut, the results should be this:

bread	1
chocolate	2
eggs	2
milk	1
avocado	1

Essentially, I would like to know how product features correlate to each other across IDs. 

Thanks in advance

[dsdesrosiers]

Thank you so, so much. This will really be useful for this project and I am sure for others as well. I really appreciate you sticking it out with me!

[andhiii079845]

Thank you for your further explanation. Now in understand it.

You have to create a second table for the slicer. 

And create a measure:

Measure = 

VAR _selectphrase = SELECTEDVALUE(Dim[Phrase])
VAR _id= SUMMARIZE(FILTER(ALLSELECTED('Fact'),'Fact'[Phrase]=_selectphrase),'Fact'[ID])
RETURN CALCULATE(count('Fact'[Phrase]),FILTER('Fact','Fact'[ID] IN _id))

The result: the phrase column is from the fact table!

You can also filter out "peanut" this will be second measure for the filter plane of the matrix.

Measure 2 = 
VAR _select = SELECTEDVALUE(Dim[Phrase])
RETURN
if(SELECTEDVALUE('Fact'[Phrase])<>_select,1,0)

To show the result of the measure:

You do not have to use this Measure 2 in the matrix. It is only for the visualization of the result. 

[dsdesrosiers]

This is brilliant - thank you so much! Exactly (almost! what I needed...). Thanks as well for the screen shots, the one showing that the two tables do NOT have a relationship is key... One issue is the second measure. Using the data we've been using... if I pick Chocolate the measures are exactly what I'd expect... 

but when I apply Measure2 to be greater than or equal to 1, Chocolate is removed but so are all the other counts... I think it's because my selected value search term is gone... the reason I care is that if I can show the counts for all BUT the search term I can show the percent of the column as well as the counts which I think may be useful. 

[andhiii079845]

Can you please check from which table is the slicer(should be dim!) and from which table is the column in the matrix(should be fact!) Also post your measure 2 definition please.

Mark my post as solution if it was helpfull, thanks 🙂

[dsdesrosiers]

Definately DIM and FACT as directed

Measure 2 =

VAR _select = SELECTEDVALUE(Dim[Phrase])

RETURN

if(SELECTEDVALUE('Fact'[Phrase])<>_select,1,0)

[andhiii079845 · Accepted solution]

The first measure is the problem, my fault: This should work:

Measure =

VAR _selectphrase = SELECTEDVALUE(Dim[Phrase])

VAR _id= SUMMARIZE(FILTER(ALL('Fact'),'Fact'[Phrase]=_selectphrase),'Fact'[ID])

RETURN countx(FILTER('Fact','Fact'[ID] IN _id),'Fact'[Phrase])

[tamerj1]

Hi @dsdesrosiers 

you need first to create a dim table (Phrases) that is connected to the main table (Phrases filters 'Table') that will be used as a slicer 

place 'Table'[Phrase] in a table visual along with the following measure 

Count =
CALCULATE ( COUNT ( 'Table'[ID] ), ALL ( Phrases ), VALUES ( 'Table'[ID] ) )

[dsdesrosiers]

Unfortunately that did not do it either. I think I may need to explain differently. Imagine a pool of people with illnesses. Pick stroke. Return a list of everybody with a stoke history first. Then return a list of all related medical issues and a count of those issues. So high blood pressure would be a comorbidity. How many people with stroke also have high blood pressure? How many people with stroke have another related issue? Etc...

[andhiii079845]

I am sorry. I do not understand this. 🙂

[andhiii079845]

You mean the distinct count per item, right? 

(Avocado is 2 not 1, or? )

But phrase in the column of a matrix and use this measure:

MEASURE =
VAR _phrase = SELECTEDVALUE(Table[Phrase])
CALCULATE (
    DISTINCTCOUNT ( Table[ID] ),
    FILTER ( Table, Table[Phrase] = _phrase)
)

[dsdesrosiers]

Thanks for the quick response...Avocado should be 1 ; and this isn't working unfortunately. What this is returning is the number of cases with Peanut, not the number of cases with the OTHER items that are in the same IDs as Peanut.