cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dhruvin Member
Member

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
sturlaws New Contributor
New Contributor

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

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

View solution in original post

5 REPLIES 5
LivioLanzo Super Contributor
Super Contributor

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

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Dhruvin Member
Member

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

@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.

Community Support Team
Community Support Team

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

@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 Member
Member

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

@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?

Highlighted
sturlaws New Contributor
New Contributor

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

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

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 496 members 4,529 guests
Please welcome our newest community members: