Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
The first measure is the problem, my fault: This should work:
Proud to be a Super User!
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!
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))
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)
You do not have to use this Measure 2 in the matrix. It is only for the visualization of the result.
Proud to be a Super User!
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.
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 🙂
Proud to be a Super User!
Definately DIM and FACT as directed
The first measure is the problem, my fault: This should work:
Proud to be a Super User!
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] ) )
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...
I am sorry. I do not understand this. 🙂
Proud to be a Super User!
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)
)
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |