topic Re: Return a list and count of related items in DAX Commands and Tips

Return a list and count of related items

Anonymous — Thu, 16 Mar 2023 18:59:33 GMT

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

Re: Return a list and count of related items

andhiii079845 — Thu, 16 Mar 2023 19:08:24 GMT

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)
)

Re: Return a list and count of related items

Anonymous — Thu, 16 Mar 2023 19:17:24 GMT

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.

Re: Return a list and count of related items

andhiii079845 — Thu, 16 Mar 2023 19:21:00 GMT

I am sorry. I do not understand this. 🙂

Re: Return a list and count of related items

tamerj1 — Thu, 16 Mar 2023 20:03:58 GMT

Hi Anonymous

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] ) )

Re: Return a list and count of related items

Anonymous — Fri, 17 Mar 2023 13:03:07 GMT

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...

Re: Return a list and count of related items

andhiii079845 — Fri, 17 Mar 2023 14:22:39 GMT

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.

Re: Return a list and count of related items

Anonymous — Fri, 17 Mar 2023 18:01:49 GMT

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.

Re: Return a list and count of related items

andhiii079845 — Fri, 17 Mar 2023 18:15:21 GMT

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 🙂

Re: Return a list and count of related items

Anonymous — Fri, 17 Mar 2023 18:22:19 GMT

Definately DIM and FACT as directed

Measure 2 =

VAR _select = SELECTEDVALUE(Dim[Phrase])

RETURN

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

Re: Return a list and count of related items

andhiii079845 — Fri, 17 Mar 2023 18:44:56 GMT

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])

Re: Return a list and count of related items

Anonymous — Fri, 17 Mar 2023 19:08:51 GMT

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!