Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have following two tables with fields:
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]))
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.
Solved! Go to 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:
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
@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?
Hi @Dhruvin,
If you set the relationship between Sites and StoreChecklist to 1:many and direction single, perhaps this measure will meet your requirements:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Sure, I have added the sample data source file and Power BI file.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |