Forum Discussion
List column is a multivalue Lookup Column - How to report individual totals
Relatively new to PowerBi - and have a query on how to achive something,
My data source is a SP List - that has multiple columns two of which are Multivalue Lookup columns.
I am trying to create a report that allows me to show amount of times an individual value exists.
So as an example if the source list columns were.
Date Restaurant Name Food eaten(lookup column)
01/01/2019 Mcds Burger, Chips, Drink, salad
01/01/2019 Freds Diner Burger, Drink
02/01/2019 My House Salad, Drink
03/01/2019 Mcds Burger, Chips
How could I create a report that on it I can show amount of times Burger was eaten (i.e 3) and obvioulsy be able to equally say how many times Burger was eaten at Mcds (2).
Hi Jc75
Create measures
if_exsit = SEARCH("Burger",MAX(Sheet4[Food eaten]),1,0) times = CALCULATE(COUNTROWS(Sheet4),FILTER(ALLSELECTED(Sheet4),[if_exsit]=1))Best Regards
Maggie
1 Reply
- v-juanli-msft
Community Support
Hi Jc75
Create measures
if_exsit = SEARCH("Burger",MAX(Sheet4[Food eaten]),1,0) times = CALCULATE(COUNTROWS(Sheet4),FILTER(ALLSELECTED(Sheet4),[if_exsit]=1))Best Regards
Maggie