Measure in Matrix making Column not filter correctly.

[LAndris]

Hi All,

I am having an issue when filtering my matrix column headers. In the Column field I have periods that are being displayed and a filter that should show filter which types of periods are being displayed.

When I filter the periods by their type I expect the columns to only show the periods that fall within that type, which was the case when using the my amount field in my facts table. However, when using my measure that is needed to choose when to calculate a sum or average from the facts table amount field, all periods are being displayed when the period is filtered by the types of periods. I believe the issue lies somewhere in my measure but I have been unable to figure it out yet. 

Below I have attached some screen shots that show what is going on

Relationships between the facts table the period table and the periodtype table appear to be correct.

Pbi Desktop Display

Measure DAX:

Any help is greatly appreciated.

Thanks, 

Louis

[richbenmintz]

Hi @LAndris ,

Could you provide the definition of Measure 2 and a sample pbix?

Thanks,

[LAndris]

Here is a link to the pbix: https://ratingdynamics-my.sharepoint.com/:u:/g/personal/landris_i4ins_com/Eaie2zb4RchFsjfB5LaLggwBq0...

The measure should be what was in the image above.

[richbenmintz · Accepted solution]

Hi @LAndris,

The Format Function is formatting Blanks() to an empty string, if you modify the measure to 

Count_And_Average_DrillThrough = 

IF(
    SelectedValue('RPT01 02_AmountType'[AmountTypeId]) <= 100 && SUM('RPT01 01_CycleTimeReport_Amount_Row'[Amount]) <> BLANK()
    ,Format(
            CALCULATE(
                        SUM('RPT01 01_CycleTimeReport_Amount_Row'[Amount])
                    )
            ,"General Number"
            )
    ,CALCULATE(
                AVERAGE('RPT01 01_CycleTimeReport_Amount_Row'[Amount])
            )
    )

you get the following result

Now you weeks are not ordered properly, but that is a whole other issue!

Thanks,