Forum Discussion

Jc75's avatar
Jc75
New Member
7 years ago
Solved

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's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity 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