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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Thulasiraman
Helper II
Helper II

Dim table not to filter

Hi

I have the following issue

I have a dim table containing a column of job numbers (1,2,3,4,5,....so on)

I have a fact table containing sale value against the job numbers.

Fact table contain only those job values against which sale invoice raised.

Both the table relationship created by job no. 

I want a matrix visual which displays all job numbers from dim table

whether sale value is found in fact table or not.  If sale value is raised,

it should show sale value otherwise blank or o.

 

I used CALCULATE(SUM('facttable'[sale amt}), ALL('dim table[job no.])

 

it gives sum total of sale value of all job numbers.

 

Pls hlep

2 ACCEPTED SOLUTIONS
selimovd
Super User
Super User

Hey @Thulasiraman ,

 

you don't have to struggle with a DAX measure. In general Power BI is hiding all rows that are blank.

But you can show them by going to the field options and selecting "Show items with no data":

selimovd_0-1656785292494.png

 

That should solve your problem.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

View solution in original post

Hey @Thulasiraman ,

 

I didn't see your reply as you didn't mention me with an @. Then I won't get notified.

Sure, you can also return a 0 with a DAX measure or a blank string, then the row will always be shown:

SUM to show in every line = 
VAR vSum = SUM( 'myTable'[myColumn] )
RETURN
IF( vSum = BLANK(), "", vSum )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

View solution in original post

3 REPLIES 3
Thulasiraman
Helper II
Helper II

Thank u so much Denis. I was strugling with dax without knowing this simple technique.

However is there any way for a dax solution, since I would require only the job numbers which is not billed.

Now I have to filter the blanks to get it. 

Thanks in advance.

Best regards.

Hey @Thulasiraman ,

 

I didn't see your reply as you didn't mention me with an @. Then I won't get notified.

Sure, you can also return a 0 with a DAX measure or a blank string, then the row will always be shown:

SUM to show in every line = 
VAR vSum = SUM( 'myTable'[myColumn] )
RETURN
IF( vSum = BLANK(), "", vSum )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

selimovd
Super User
Super User

Hey @Thulasiraman ,

 

you don't have to struggle with a DAX measure. In general Power BI is hiding all rows that are blank.

But you can show them by going to the field options and selecting "Show items with no data":

selimovd_0-1656785292494.png

 

That should solve your problem.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.