Forum Discussion

LAndris's avatar
LAndris
Helper I
5 years ago
Solved

Measure in Matrix making Column not filter correctly.

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

 

 

Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    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,

3 Replies

      • richbenmintz's avatar
        richbenmintz
        Resident Rockstar
        5 years ago

        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,