So, my google foo totally failed me on this one so after a couple of days, I finally got it working myself. I thought I had better post my use case and solution as it may be of interest to others.
So I have 2 tables:
A List of accounts who are supposed to submit data to us each month "Rebate T1 Partners"
A data table containing the data that has been submitted. "CD_T_CUSTOMER_DATA_HEADER"
Now I wanted to select a month in a slicer and have 2 grids. who has submitted data with some details about it, and a list of accounts that have not submitted anything.
Who has submitted in a particular month is easy, so I wont go into it. Who has not submitted was a headache so hear goes 🙂
Step one: Create a crossjoin table of distinct months reported from the data table and the list of accounts
CrossJoinTable = (CROSSJOIN(DISTINCT(CD_T_CUSTOMER_DATA_HEADER[DATAPERIOD]),ALLNOBLANKROW('Rebate T1 Partners')))
This generates a table of all partners and all months.
Step 2: Rename the month column to avoid conflict later & create a new column as a key with the month reported and the account code
Key = [DATAPERIOD crossjoined]&[T1 Code]
Step 3: Create the same key in the data table with a different name
Header Key = [DATAPERIOD]&[CUSTOMERCODE]
Step 4: Create a relationship between the 2 key columns
Step 5: Create a new table with a left join of the crossjoin table from step one and the data table
T1 Submissions = NATURALLEFTOUTERJOIN('CrossJoinTable',CD_T_CUSTOMER_DATA_HEADER)
This has now created a table of all possible month/account combinations with actual submitted data only populated on the rows where submitted.
You can now use this table to create a grid and slicer using the crossjoined month as value field. Use the non crossjoined month = isblank as the visual filter.
Now when you select a month in the slicer, the visual will show any account that has blank data for the month.
Phew...if anyone has an easier way, happy to hear it for future reference! If not, enjoy your new report 🙂
