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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Creating a Report with a Filter Based on Presence of Data

Capture.JPG

 

I need to create a report based on this file. The report will have a date filter. When a user selects “March 1, 2019” The visualization will show Store 999 and Store 076 and not Store 456, because sales are 0 for that location in all categories.

So,  something like SELECT Location WHERE Date=(Param) AND (Boot Sales IS NOT 0 AND Jackets Sales IS NOT 0 AND Pants Sales IS NOT 0)

What would be the best solution?

Thank you!

5 ACCEPTED SOLUTIONS
Nathaniel_C
Super User
Super User

Hi @Anonymous ,

Please see the pictures below. First is a truncated table with some of your data.  The second is a date slicer on the visual card. The picture also contains the measure I wrote using CALCULATE() and CONCATENEX().

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

Store Sales2.PNG

 

Store Sales.PNG





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

Proud to be a Super User!




View solution in original post

Hi @Anonymous ,

 

I just rebuilt my measure again on a clean pbix to test and it still works.

Sales Today = CALCULATE(CONCATENATEX(Sales,Sales[Location], ", "), FILTER(Sales,Sales[Boot Sales]>0 || Sales[Jacket Sales]>0 || Sales[Pant Sales]>0))
 
I am able to show it as a string. I wonder if you try only three locations as a QA test and see how it goes. Next, I would double check the query that used to bring the data in, and make sure that all columns are whole numbers.  Depending how urgent this is, I might even go so far to add a change type step, and redo them all.  I looked over your measure, and it looks fine, so it might be the data, which would be a good thing to clean up.
 
Good luck!
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel




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

Proud to be a Super User!




View solution in original post

@Anonymous ,

Sorry, my miss read. If you use UNICHAR(10) with no quotes after the comma (instead of the ", ") it will create a list in a card.

 

Unichar 10.PNG

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




View solution in original post

@Anonymous ,

Ok, this is cool. Created a second page, added a State column to my mockup table, put the map visualization on it on the new page, put the same table and filter on that page and filtered table for Total Sales greater than zero.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

Total Sales map.PNG





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

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

BTW, I was able to solve the problem of displaying locations by simply using the Table visual instead of the Card. Used your original measure and it worked fine.

View solution in original post

10 REPLIES 10
Nathaniel_C
Super User
Super User

Hi @Anonymous ,

Please see the pictures below. First is a truncated table with some of your data.  The second is a date slicer on the visual card. The picture also contains the measure I wrote using CALCULATE() and CONCATENEX().

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

Store Sales2.PNG

 

Store Sales.PNG





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

Proud to be a Super User!




Anonymous
Not applicable

 

 

@Nathaniel_C  Thank you very much for the solution! Is it possible to show the Store values as a list, not as a string.

Like in the screen shot below.

dax.JPG

 

Hi @Anonymous ,

 

I just rebuilt my measure again on a clean pbix to test and it still works.

Sales Today = CALCULATE(CONCATENATEX(Sales,Sales[Location], ", "), FILTER(Sales,Sales[Boot Sales]>0 || Sales[Jacket Sales]>0 || Sales[Pant Sales]>0))
 
I am able to show it as a string. I wonder if you try only three locations as a QA test and see how it goes. Next, I would double check the query that used to bring the data in, and make sure that all columns are whole numbers.  Depending how urgent this is, I might even go so far to add a change type step, and redo them all.  I looked over your measure, and it looks fine, so it might be the data, which would be a good thing to clean up.
 
Good luck!
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel




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

Proud to be a Super User!




@Anonymous ,

Sorry, my miss read. If you use UNICHAR(10) with no quotes after the comma (instead of the ", ") it will create a list in a card.

 

Unichar 10.PNG

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C 

It does display it as a list now. But I have a couple of issues with it that I must solve: I can't scroll through it (I assume because it's a single row card) and I can't format it. So everything is centered. Another problem is that if a list is too long, then the last line just turns into a super long string.

dax.JPG

Also, I have another visual on my report. it's a map showing states where those locations are. Driven by the same Date Slicer. Obviously now it goes by unfiltered location. Is there any way to apply your measure to that map to filter the results?

I am sorry to be a pain with all the questions 🙂

dax2.JPG

 

BTW, I replied to your message in the private Inbox. I hope it worked, as today I am having problems with my network and form loading in general.

 

@Anonymous ,

Ok, this is cool. Created a second page, added a State column to my mockup table, put the map visualization on it on the new page, put the same table and filter on that page and filtered table for Total Sales greater than zero.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

Total Sales map.PNG





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

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C 

Thank you so much for your help! I am going to try to figure it out and implement. I will report 🙂

Ana

 

@Anonymous ,

 

Still working on this, but this is down and dirty. 

Total Sales = Sum(Sales[Boot Sales])+Sum(Sales[Jacket Sales])+SUM(Sales[Pant Sales]) New measure.
Created a table, put Location, Date and Total Sales on the Values and Filtered it for Total Sales >0.
Then, stretched the first column, and shrunk the visualization smaller than the column.  Not pretty, (You can see the scroll bar)but if you need it quick...
 
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Total Sales shown.PNG





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

Proud to be a Super User!




Anonymous
Not applicable

BTW, I was able to solve the problem of displaying locations by simply using the Table visual instead of the Card. Used your original measure and it worked fine.

Anonymous
Not applicable

@Nathaniel_C  Sorry for the confusion! I deleted my question about data type conversion (i did have an empty column with Text data type). I was able to fix it.

 

 My other question was about how to display them in a list (like a multi-value card), instead of a string ( pls see my message above).

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors