How to populate missing rows dynamically for calculations?

[JonV]

I'm trying to create a table in Power BI that, at its core, displayed the frequency with which certain items are purchased. This data is being pulled from MS SQL and looks something like this:

From there, various calculations will be applied to each row. My trouble is that those calculations (measures) still need to run on the rows where the frequency of a given quantity was 0. Thus, what I figure I'm needing to do is add rows with a frequency of 0 for all quantities from 1 to the max value for each item.

My immediate thought is that this is much like completing a table with dates by joining to a date table. While I suspect this is the right approach, I'm running into 2 issues:

1. The max frequency is not consistent. For some widgets it might be 7. For others it might be 5,000. So I generate a quantity table with a max of 5,000. Thus, when creating a table of values to join on and telling PBI to show items with no data, it displays 5,000 rows. I need it to truncate at the max value for the widget selected.
2. It doesn't actually perform calculations. Even though it shows the rows, there's no data in them to calculate with.

So is there any way I can get the filtered table to include the omitted rows up to the max and populate it with 0's so the calculations will run?

[v-cherch-msft]

Hi @JonV

It seems you may create a measure like below. Attached the sample file. If it is not your case,please share the sample data and expected output.

How to Get Your Question Answered Quickly

Measure = CALCULATE(SUM(Table1[Frequency]))+0

 

Regards,

Cherie

[JonV]

Thanks Cherie. That solves one problem. However, I still have the problem of needing the quantity list to truncate at the max value of the frequency. Otherwise, when I convert things to a chart, the data for items that only have a few lower values will be scrunched up at one side.

[v-cherch-msft]

Hi @JonV

I cannot fully understand it. Could you provide the simplified data and expected output for us?

How to Get Your Question Answered Quickly

Regards,

Cherie

[JonV]

The issue is that, in order to get it to populate the rows for which there is no quantity, we've joined it to a quantity table. However, as I noted in my OP, the max quantity for some items varies. Sometimes it's 7. Sometimes it's 1,500. So consider what happens when we turn that table into a graph: In the response you provided previously, you created a quantity table with a max of 30. You can clearly see that when it's graphed, this creates extra points where the graph is flat as there's no actual data there; The real data gets squished up to the left. Now imagine that if the quantity table it's joined to isn't 30, but 1,500. The chart would be unreadable. So my question is how to get it to only display the graph to the max value there actually is data for? I've tried creating the quantity table with GENERATESERIES(1, MAX('Frequency'[Quantity])), in hopes that it would dynamically create the table and that the MAX function would be affected by the slicers, but it doesn't seem to be.

[v-cherch-msft · Accepted solution]

Hi @JonV

You may try below measure:

Measure 2 =
VAR a =
    MAXX ( ALLSELECTED ( Table1 ), Table1[Quantity] )
RETURN
    IF (
        MAX ( 'Table'[Quantity] ) <= a,
        CALCULATE ( SUM ( Table1[Frequency] ) ) + 0
    )

Regards,

Cherie

[JonV]

Super slick. Works very well in my simplified test file. Trying to get it to work on the actual full dashboard. I'll mark the above as a solution once I integrate it.

[v-cherch-msft]

Hi @JonV

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. 

Regards,

Cherie