To show value without data rows in source data in specific months

[cj_oat]

Hi,

I have 2 tables below that I have already created relationship by using "Time Period" and "Product Group" to map and use "Related ('Table 1'[Count])" to pull value from 'Table 2'

I'm trying to make "1" shows to all month under that "Time Period" and under that "Product Group" but as you may see in the screenshot below which I've highlighted in yellow, some of them shows BLANK since there is no data rows in the source data (e.g. in 'Table 1' for month 2024003, there is no SKU number 34 and 40 while it might available in other months)

is there any formula to make it shows the value ?

Table 1
Product Group	SKU	Month	Time Period	Related ('Table 1'[Count])
100001	28	2024003	241	1
100001	29	2024003	241	1
100001	30	2024003	241	1
100001	31	2024003	241	1
100001	32	2024003	241	1
100001	33	2024003	241	1
100001	36	2024003	241	1
100001	38	2024003	241	1

Table 2
Product Group	Count	Time Period
100001	1	241

[cj_oat]

Hi @FarhanJeelani ,

Thank you so much, it works!

[FarhanJeelani · Accepted solution]

Hi @cj_oat ,

To make the "1" appear for all months under a specific "Time Period" and "Product Group," you can use the following approach in Power BI or DAX:

1. Create a Full Combination Table: Generate a table with all possible combinations of Product Group, SKU, Month, and Time Period. You can do this using a cross-join between relevant columns.

2. Fill Missing Data: Use the following DAX formula for a calculated column or measure:

Count_Filled = 
IF(
    ISBLANK(RELATED('Table 1'[Count])),
    LOOKUPVALUE('Table 2'[Count], 'Table 2'[Product Group], 'Table'[Product Group], 'Table 2'[Time Period], 'Table'[Time Period]),
    RELATED('Table 1'[Count])
)

This will pull the Count value from Table 2 if the Table 1 count is blank.

• Ensure Relationships are Properly Defined: Make sure relationships between Table 1 and Table 2 are active and correctly mapped through Time Period and Product Group.

With this setup, all missing rows in Table 1 will automatically get values from Table 2, ensuring "1" shows up for all months. Let me know if further clarification is needed!

Please mark this as solution if it helps. Appreciate Kudos.