Forum Discussion
Calculate STDEV include 0 values - PROBLEM IS WITH MY DATA LINEAGE
try on power query create 2 table, one with the range of dates you want, another with all posible combination of location, stafftype etc etc, with a column tha concatenate all columns as a reference, now on both tables create a dummy column named "ref" and with a "." as data on all records xD, merge both tables using that last column as reference you will have all possible locations ,stadtype etc combination by each date on the range, load this to the data model, now using dax create a new column to read or sum the data from the fact table and populate the new reference table you did, or using a merge again with the fact and new table on power query both final apporach should work fine
try on power query create 2 table, one with the range of dates you want, another with all posible combination of location, stafftype etc etc, with a column tha concatenate all columns as a reference, now on both tables create a dummy column named "ref" and with a "." as data on all records xD, merge both tables using that last column as reference you will have all possible locations ,stadtype etc combination by each date on the range, load this to the data model, now using dax create a new column to read or sum the data from the fact table and populate the new reference table you did, or using a merge again with the fact and new table on power query both final apporach should work fine
the first option give you the oportunity to use dax to change blank results to 0, on power query you should use replace vaue to replace blank results by 0
Hi Stefano,
Thanks for your advice. I followed your suggestion, though I did't use a concatenated column (I was unclear the benefit). The dummy column method was perfect for creating my "0" data. It is avery large set but seems manageable.
Thanks!