Many-to-many dimension - blank row behavior

[BartVW]

hi all

In my data set, i have a fact table that has a many-to-many relation to a dimension table. 

Fact table has a foreign key to the dimension table, and eg sales amount as a measure.

Dimension table has zero, 1 or multiple entries for the fact table key.

Relationship has been set up as many-to-many and filter direction = 'BOTH'. 

If I now create a report grouping the sales amounts by a column of the dimension table, I do get correct sales amounts for each column value in the dimension table (and repeated values if the dimension tables has multiple entries for a key), but I don't get the blank row in the report (table) layout (I refer to the blank row concept described here). The grand total does show the sales amount incl fact table rows that don't have an entry in the dimension table, and if I put a filter to exclude blank column values from the dimension table, the grand total does adjust correctly, so it seems the blank row does exist. 

Is there a way to show this blank row on the report, or am I perhaps modeling my data set in the wrong way?

[jcalheir]

Would this make sense? This way you can avoid many to many relations

[BartVW]

I don't think so. In my data, the measure (price in your example) is linked only to a warehouse task, not to the combination of warehouse task and service code. I think that if I implement your solution, I would double count those cases where a warehouse task has more than 1 service code.

[jcalheir]

Yes but that would be as well a facts table, (WarehouseTasks) and woul make a distinctcount and create measures on top of that distinctcount measure.

If you want, you could share you pbi file and i could provide an example

[BartVW]

Thanks. I can try and prepare a mockup pbix with my scenario. But before I do: will your solution also work for those cases where there is no service code at all? Would that introduce records with a blank key in the fact table? And is it really best practice in power bi to handle this through a measure (it cannot be distinct count as I need to be able to sum up the quantities)?

[jcalheir]

I dont entierly know your model so i cant say it will not show the blank row, but a 1-many relationship will help you debug better, and if the row apears you will find why it does easyer.

To you other question, it depends on what you want to sum. 

In the model i presented you can:

count the number of warehouses by Countrows('Warehouses')

Count distinct services by Countrows('Services')

Count nr of warehouse services by Countrows('facts')

Sum all entrys of your measure by Sum('facts'[measure])

Hope it helps

[jcalheir]

A good rule of thumb is to avoid many to many relationships and BOTH cross filter direction.

Can you share some details on your your dimensions and fact table and se if we can model it differently?

[BartVW]

The topic of the fact table are picking tasks in a warehouse, and the dimension tables holds so-called service codes. These are codes that specify what additional VAS (value added services - things like labeling, repacking, ..) need to be applied. Most warehouse tasks have no services, some have one service and some have multiple services.  Conceptually, I feel the modelling question is similar to applying tags to eg emails, where you can have emails that have no tag, one tag or multiple tags (eg categories in Outlook), resulting in a many-to-many relation from the table with the tags to the table with the emails.