show blank value in matrix

[goyalsa]

Hi All,

I am not able to get the week that has a blank value. I already try to select "show items with no data" but still the same result.

Thanks in advance!!

[APavlovs]

@goyalsa Do you have a Date column in the original table? Might be the relatioship since its Many to Many right now.

[goyalsa]

In the original table, I have the loading week, week forecast filled in. 

[mussaenda]

Hi @goyalsa ,

You need a date table, create a relationship to your fact table

then use the data from the date table to the matrix.

Hope this helps.

[APavlovs]

Any chance you could add a sample.pbix that way it will be easier to debug the situation.

[goyalsa]

https://1drv.ms/u/s!ApGsTx0zWYrbiVCIq9AGfrSG9VcH?e=c5085a 

Hi @APavlovs,

Please find the link to access the file. I am not able to upload the file here, not sure.

Kind regards,

Sahil

[Ashish_Mathur]

Hi,

In another post, you mentioned that you have created a Date Table - i do not see that in your PBI file.  Please create that Table, ensure that you have a Date table in Table1 as well.  Also, in the Date Table, also create a Week number and Year column.  Share the download link of the revised PBI file.

[goyalsa]

Hi, @Ashish_Mathur,

In the sample file, I did not create it but create it in my original file. 

Anyway, I will create a data table in the sample file too.

https://1drv.ms/u/s!ApGsTx0zWYrbiVCIq9AGfrSG9VcH?e=Zdi3TE

Thanks in advance!!!!

Safe regards,

Sahil

[Ashish_Mathur]

As requested in my previous message, just as you have a Date column in the Calendar Table, there should be a Date column in Table1 as well.  Furthermore, the relationship should be Many to One and Single between the Date column of both tables.  Incroporate these changes and share the revised link. 

[APavlovs]

Hi @goyalsa Have you created a Date table? If you have created just a new column from the original dataset then my guess is that it does not have any reference to weeks that don`t have any data.

If you create a Date Table you can use that as the date dimension and it will include all the weeks with BLANK values.

Basic sample here - https://files.fm/u/vcrmuytne

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

[goyalsa]

Hi @APavlovs,

I have created a Date table and made a relation with the other table (Loading week).

The column headers show the loading week from the Date table, and the rows show the status from another table.

[v-rzhou-msft]

Hi @goyalsa 

I agree with APavlovs 's reply. Add YearWeek from your Date table instead of YearWeek from your Fact table. Then use show items with no data.

First visual use YearWeek from Date table and second visual use YearWeek from Fact table.

Best Regards,
Rico Zhou

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

[goyalsa]

Hi @v-rzhou-msft,

Thanks for your inputs. I just added the date table and column Yearweek from the date table. But still getting the same result. I am not sure where I am lacking in it 😞

https://1drv.ms/u/s!ApGsTx0zWYrbiVCIq9AGfrSG9VcH?e=Zdi3TE

Link to review.

Thanks in advance!!

[v-rzhou-msft]

Hi @goyalsa 

I checked your sample file, and I think I find the reason that cause your lose in 202137,202138.

You filter 202130 in your "WeekForecast Field In" Slicer. In your Fact Table 202137 and 202138 are in 2021226 group in "WeekForecast Field In" . So you couldn't find it if you filter 202130 in your slicer.

Here I remove the filter in slicer, result is correct.

I suggest you to change the Loadingweek column type (in Date table)  to Whole number, to make it to be the same as the one in your Fact table.

Try this code.

Loadingweek = YEAR(Dates[Date])*100+WEEKNUM(Dates[Date])

Best Regards,
Rico Zhou

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

[goyalsa]

Hi @v-rzhou-msft,

The insights are helpful but once I filter Week Forecast Filled in 202130 then it shows me the same result as before.

Week 202137, 202138, and 202141, 202142 are missing.

[v-rzhou-msft]

Hi @goyalsa 

I think this should be caused by relationship. You can try to build a measure. Use this measure instead of column in value field in Matrix.

Measure = SUM(Table1[Forecasted Demand])+0

Result is as below.

Best Regards,
Rico Zhou

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

[goyalsa]

Hi @v-rzhou-msft,

This is looking fine to me but the slicer filter not working at my side.

Can you please share the file link so I can take a look.

Many thanks in advance!!

[v-rzhou-msft]

Hi @goyalsa 

I try this way in your sample file and it works well in it. Here is my sample file.

Best Regards,
Rico Zhou

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

[goyalsa]

Hi @v-rzhou-msft,

I saw the sample file you have shared, but here the slicer filter is not working because,

- In your previous reply, the week forecast is 202130 but still, the week starts before 202130.

- In the sample file you shared, I can see the same problem.

Still at the same point 😞

[goyalsa]

Hi @v-rzhou-msft,

If we can find out after the selected week forecasted fill in 202130, the matrix starts with 202130, Then we can reach the solution.

[v-rzhou-msft · Accepted solution]

Hi @goyalsa 

I tried to build measures to filter the visual or change the code, but due to relationship, it is not a good way to achieve your goal.

Here I suggest to use filter in Loadingweek in Filter Field manually. If you select 202130 in slicer, enter 202130 in filter as below. Result will start from 202130.

Best Regards,
Rico Zhou

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