Forum Discussion

BartVW's avatar
BartVW
Icon for Helper I rankHelper I
4 years ago

Many-to-many dimension - blank row behavior

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 ...
Need Help
BartVW's avatar
BartVW
Icon for Helper I rankHelper I
4 years ago

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)?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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