How can I show value of one table which is not availble in another table with dynamic filtering?

[Dhruvin]

I have following two tables with fields:

1. Stores Checklist
   1. SiteId
   2. StoreName
   3. DateModified
2. Sites
   1. ID
   2. StoreName

I have applied DateModified Slicer in a report.

I want to find out value for stores not submitted. Means, From, sites table find out the name of the stores which are not available in table Stores checklist.

I can easily do it with EXCEPT function in Power BI. But, the challenge is I want value of Store Not Submitted in dynamic manner based on date slicer. Ex. If In January 5 stores have submitted then 15 are not submitted.

Currently, below are the formula which are not work as expected.

StoresNotSubmittedWeekly = EXCEPT(VALUES(Sites[Id]),VALUES('Stores Checklist'[SiteId]))

Can anyone suggest me, how to achieve this stuff in Dynamic manner?

 

Sample Data Source and Power BI file is here:

Download Sample Power BI and Excel file

As per sample data if user select January Month then, stores not submittes should be C to AZ from sites table.

[v-yuta-msft]

@Dhruvin,

---

I can easily do it with EXCEPT function in Power BI. But, the challenge is I want value of Store Not Submitted in dynamic manner based on date slicer. Ex. If In January 5 stores have submitted then 15 are not submitted.

Currently, below are the formula which are not work as expected.

StoresNotSubmittedWeekly = EXCEPT(VALUES(Sites[Id]),VALUES('Stores Checklist'[SiteId])) 

As per sample data if user select January Month then, stores not submittes should be C to AZ from sites table

---

Your clarification about the requirement is not so clear. Could you share more details?

Regards,

Jimmy Tao

[Dhruvin]

@v-yuta-msft: - My requirement is to find out the stores who hase not submitted any responses. I want to show and list down them in a table.

Example: StoreChecklist table have a column for siteId and store name which has store name A to O. 

I have master table for all the stores named "Sites" which has a value of stores from A to AZ.

So, according to my requirement stores not submitted = The name of stores which presents in Sites table but not in StoreChecklist table.

If I use the formula I get the result.

StoresNotSubmittedWeekly = EXCEPT(VALUES(Sites[Id]),VALUES('Stores Checklist'[SiteId])) 

But here is a challenge of date slicer.

If use selects only date range of January from the the slicers then the Stores not submitted value are: Stores C to AZ. 

How do I achieve this?

Any idea?

[sturlaws · Accepted solution]

Hi @Dhruvin,

If you set the relationship between Sites and StoreChecklist to 1:many and direction single, perhaps this measure will meet your requirements:

storesNotSubmitted = countrows(EXCEPT(values(Sites[Site ID]);VALUES(StoresCheckList[SiteId]))).

Create a table visual with Sites[SiteID] or Sites[Name] and the measure,
and the table will dynamically show the stores which are not present in 'StoreChecklist'-table

best regards,

Sturla

[LivioLanzo]

Hi @Dhruvin are you able to post a sample of your dataset?

[Dhruvin]

@LivioLanzo - 

Sure, I have added the sample data source file and Power BI file.

Download

Here my expected output is - If user select January then stores not submitted shoud be C to AZ from sites table.

Feel free to tell me if you need anything else.