Hello everyone,
I have a data model of 2 fact tables: A rate table (a set of the same rates for CY2024, and another set of the same rates for CY2023), and a member table with member counts for Jan-Dec 2023 and Jan 2024 only. I have created several dim tables to join the 2 fact tables, including the dimYear. When I join the 2 fact tables on this dimYear, I also have blank rows for Feb-Dec 2024, which almost double the rows of my pivot table. How can I remove these blank rows from my pivot table?
I'm new to this course and scrambling to set up the new workbook for 2024. I would appreciate any help or ideas. Thank you!
Quynh.
Hi Quynh,
Can you provide a screenshot of your data model?
Hi Catalin - Here is the data model: I think the problem is that I have "Year" and "Month" in the Member table (because member counts change each month), but the Rate is the same for each "Year" in the Rate table. I hope there is some way to work around it.
I think you should check the measures, if there is no fact data for a specific period, those periods should be automatically removed by pivot.
For example, a measure like this one will always show all items, even if there is no data in fact table:
Measure1:=IF(ISBLANK(SUM(Table1[Value])), 0, SUM(Table1[Value]) )
Hard to see what happens in your case, can you upload a sample file that replicates the problem?
This is the only measure I have in this data set:= SUMX(Rates, Member_accr[Sum of Member Count]*Rates[Rate])
Attached is a very small subset of the data as an example: As you see, for each month there are always 2 years. And I don't even have Feb-Dec 2024 member count anywhere in my member table 
Can you please upload a sample file with anonymized data?
Thank you
Attached is the data model abstract.
I also need to bring in Account Title column from Dim_Account_Title table and Period CC column from DimPeriod table, but that also create a lot of duplicates.
This is my first time creating a data model because my excel formulas are getting ridiculously long.
I appreciate any help.
Rates should have a direct relationship with Membership table, (Rates should be a dimension table), not filtered through other dimensions.
I created a unique key in Rates and Membership and created a relationship based on this key.
Membership does not have LOB and Company fields, so you can't use these fields in the pivot table until you have this relationship.
File attached
Thank you so much! This is very helpful!
