Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
edant
Regular Visitor

Display all companies list with no sales according to date slicer

Hello,

 

I have a table that contains the sales of companies, each sale is a different record on the table.

I also have a date slicer that I work with to see the sales between selected dates.

Now, I want to display instead only the companies which made no sales in the selected time. (of the slicer)

The problem is that whenever I change the dates in the slicer, the companies which made no sales - disappear.

Is it possible to do so?

 

Regards,

Edan

5 REPLIES 5
DataInsights
Super User
Super User

@edant,

 

You can achieve this with a measure and visual filter.

 

Company With No Sales = IF ( ISEMPTY ( FactTable ), 1 )

 

Add DimCompany[Company] to a table visual and use the above measure as a visual filter:

 

DataInsights_0-1708451692344.png

 

This solution requires a star schema:

 

DataInsights_1-1708451726365.png

 





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

Proud to be a Super User!




v-yohua-msft
Community Support
Community Support

Hi, @edant 

I've created a sample table:

vyohuamsft_0-1708418528452.png

 

 

Then you can find the Sale in the filter next to it and filter for the companies with a Sale value of 0

1.1.png

If you move the slicer by date after filtering out companies that don't have sales, you can see companies that didn't have sales during a certain date period

vyohuamsft_2-1708418528455.png

 

 

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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

Thank you for your answer.
In your solution, you declared to each date the company and the amount of sales that are being made.
I have hundreds of companies and it would be unreasonable to create for each date a list with all of the companies and the sum of the sales that they made.
Is there another way to do so without declaring the amount for each day?
I meant that if a specific date doesn't have a record of sale - it should be displayed on the list.
I have a table of dates and a table of full companies.
The third table contains records of each sale per company.
I wish to display only the companies that didn't make any sales between certain dates.

edant
Regular Visitor

It works only if you are not using a slicer.
If your slicer uses dates (between), the companies reduce.

amustafa
Super User
Super User

 

In your visual, right mouse click and select "Show items with no data" as shown below.

 

amustafa_0-1708361079932.png

 





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

Proud to be a Super User!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.