Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
AliPoTD
Advocate II
Advocate II

Matrix table shows more months than Date filter due to Measure issue

Hello all,

 

I'm fairly new to Power BI and have run into a problem that I hope someone can help me with please?

 

I have Table A which is an unrelated table of data:

FromToBonus
3.253.300.15
3.303.350.3
3.353.400.45

 

I have a matrix table showing columns of month end dates, with varying rows of information underneath gathered through other measures and table data:

AliPoTD_0-1722875934617.png

 I have filtered the table to show month end dates from January to May 2024, but since adding a Measure, I now have columns from Aug 23 to July 24, the full date range of my data.

The code I've used for my measure is:

Protein% Price Per Litre Payable =
VAR PPL = sum('zMASTER Milk Data'[Protein% Litres]) / [Total Litres] * 100
RETURN
-- PPL
CALCULATE(
    SUM(    'Protein Bonus'[Bonus]),
    PPL <= 'Protein Bonus'[To]
    && PPL >= 'Protein Bonus'[From])
 
I know the issue relates to the CALCULATE section of the code, as if I exclude this from the measure and just show the VAR result, it shows values only for Jan to May 2024 correctly as expected.
 
Can anyone help explain why it is doing this for the measure? I realise I haven't fully filtered it to show for only the Slicer dates, but unsure why it is only doing it for this particular measure?
 
Many thanks for any help 🙂
1 ACCEPTED SOLUTION
AliPoTD
Advocate II
Advocate II

It's easy when you know how, ha!

A very simple solution to the problem was to simply wrap an IF statement around the measure, to check whether a value was present e.g.:

Protein% Price Per Litre Payable =
VAR PPL = sum('zMASTER Milk Data'[Protein% Litres]) / [Total Litres] * 100
RETURN
IF( ISBLANK([Total Litres]),
     BLANK(),
     CALCULATE(
        SUM('Protein Bonus'[Bonus]),
            PPL <= 'Protein Bonus'[To]
            && PPL >= 'Protein Bonus'[From])
        / 100)
So basically, it only shows for any value showing on the table with a TOTAL LITRES value. May not be the prettiest of solutions but it works. Interested to know if there is a neater solution (best practice) though if anyone has any thoughts 🙂 

View solution in original post

4 REPLIES 4
AliPoTD
Advocate II
Advocate II

It's easy when you know how, ha!

A very simple solution to the problem was to simply wrap an IF statement around the measure, to check whether a value was present e.g.:

Protein% Price Per Litre Payable =
VAR PPL = sum('zMASTER Milk Data'[Protein% Litres]) / [Total Litres] * 100
RETURN
IF( ISBLANK([Total Litres]),
     BLANK(),
     CALCULATE(
        SUM('Protein Bonus'[Bonus]),
            PPL <= 'Protein Bonus'[To]
            && PPL >= 'Protein Bonus'[From])
        / 100)
So basically, it only shows for any value showing on the table with a TOTAL LITRES value. May not be the prettiest of solutions but it works. Interested to know if there is a neater solution (best practice) though if anyone has any thoughts 🙂 
Anonymous
Not applicable

Hi @AliPoTD ,

 

Thanks for the reply from @amitchandak , please allow me to provide another insight: 

 

This may have something to do with the relationships between your data tables. You need to make sure that you have defined the relationships between your tables correctly. Typically, you have a one-to-many relationship between your date and fact tables. Also, check the filter direction of the relationship. If Protein Bonus is an unrelated table, the measure may not be applying the filter correctly.


If this doesn't solve your problem, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. problem and help you.

 

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

amitchandak
Super User
Super User

@AliPoTD , Based on what I got

 

This will work like

 

 filter(all([PPL] ) , PPL <= 'Protein Bonus'[To]
    && PPL >= 'Protein Bonus'[From])
 
 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi, thanks for the advice, but this hasn't worked 😞

I get a syntax error due to filtering on a VAR value, see image:

AliPoTD_1-1722930657305.png

I have changed the code to the following so that it is drawing directly from the unrelated table, but I end up with the same initial error:

AliPoTD_2-1722930750475.png

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors