Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Display values that are not selected in the segmenter

Hello, good afternoon

I have a dashboard that shows information about restaurants and their products sold and a part of the report should show the restaurants that do not sell the selected product.

The model is like this

Yeztrom_0-1648241896435.png

There are two dimension tables, one for products and one for restaurants and there is a table of facts vw_product_prices in which the products sold are recorded day by day and it is specified in which restaurant it was sold.

At the beginning of the report there is a slicer with the list of products and the visual element that I want to create is a table that shows a list of the restaurants that did not sell the product selected in the slicer, I would like to know if there is a way to achieve this.

Best regards.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Syndicate_Admin ,

     

    I have created a data sample:

     

    You could create a flag measure:

    Flag = 
var _t= ADDCOLUMNS(SELECTCOLUMNS('Product_Prices',"Rest_id",[master_restaurant_id],"Prod_id",[master_product_id]),"Prod_name",LOOKUPVALUE(Products[Product],Products[master_product_id],[Prod_id]  ))
var _allsold=CONCATENATEX(FILTER(_t,[Rest_id]=MAX('Restaurant'[master_restaurant_id])),[Prod_name],",")
var _allseleted= CONCATENATEX('Products',[Product],",")
return IF(CONTAINSSTRING(_allsold,_allseleted),1,0)

    And apply it to filter pane, set as "is 0":

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Use CROSSFILTER(,,none) in your measure to emulate a disconnected table.  Then you can show dimension values that do not have facts.   (for simple scenarios you can also use the "Show items with no data" flag.)

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Syndicate_Admin ,

     

    I have created a data sample:

     

    You could create a flag measure:

    Flag = 
var _t= ADDCOLUMNS(SELECTCOLUMNS('Product_Prices',"Rest_id",[master_restaurant_id],"Prod_id",[master_product_id]),"Prod_name",LOOKUPVALUE(Products[Product],Products[master_product_id],[Prod_id]  ))
var _allsold=CONCATENATEX(FILTER(_t,[Rest_id]=MAX('Restaurant'[master_restaurant_id])),[Prod_name],",")
var _allseleted= CONCATENATEX('Products',[Product],",")
return IF(CONTAINSSTRING(_allsold,_allseleted),1,0)

    And apply it to filter pane, set as "is 0":

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.