Filter in measures doesn't work while applying the measure to charts

[DearestYoki]

Hi all,

I encountered a problem  while applying a filtered measure to the chart. I managed to filter the sum of distinct [Amount] by their respective Flag name, latest year and latest quarter. I have created separated measures for year and quarter and I'm sure they are correct.

Measure =

var MaxYear = [MaxYear]

var MaxQuaSP = [MaxQuarter_SP]

var MaxQuaBC = [MaxQuarter_BC]

var MaxQuaIR = [MaxQuarter_IR]

var MaxQuaGIRO = [MaxQuarter_GIRO]

var MaxQuaBD = [MaxQuarter_BD]

return

IF(VALUES('Flag Index'[Flag])="Bounced Cheque",

IF(ISFILTERED(Main[Year])||ISFILTERED(Main[Month])||ISFILTERED(Main[Quarter]),

SUMX(DISTINCT(SELECTCOLUMNS(FILTER(Main,Main[Year]=MaxYear&&Main[Flag]="Bounced Cheque"),"A",Main[Amount])),[A]),

SUMX(DISTINCT(SELECTCOLUMNS(FILTER(Main,Main[Year]=MaxYear&&Main[Quarter]=MaxQuaBC&&Main[Flag]="Bounced Cheque"),"A",Main[Amount])),[A])),

IF(VALUES('Flag Index'[Flag])="Short Payment",

... //The following code are the same as the first part, just changing flag name and Quarter

Then this is the chart I got, which is not really filtered by [MaxQuarter] and [MaxYear]

The chart only shows correct value when I add additional slicers:

I tried Main[Year]=2019&&Main[Quarter]="Q4" as filter but still doesn't work in the chart. Could anyone help me with this?

Thannks and Best Regards,

Yoki

[amitchandak]

@DearestYoki , do you have date. If so use time intelligence with date table

example

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))

If not move qtr-year to a different table and create a rank and use it

Qtr Rank = RANKX(ALL(‘Date’),’Date’[Start Of Qtr],,ASC,Dense)

This Quarter = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Qtr Rank]=max(‘Date’[Qtr Rank])))
Last Quarter = CALCULATE([Sales], FILTER(ALL(‘Date’),’Date’[Qtr Rank]=max(‘Date’[Qtr Rank])-1))

refer: https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[DearestYoki]

Thanks for your reply. How could I add "DISTINCT" into a CALCULATE function since I only want sum of distinict amounts?

Yoki

[v-rzhou-msft · Accepted solution]

Hi @DearestYoki 

Due to I don't know your data model, I have to build a sample table to have a test.

Add an Index column in Power Query Editor.

If your table has Date column, you can build a Rank column filtered by Outlet Name, Statement, Year and Qtr.

Rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Outlet Name] = EARLIER ( 'Table'[Outlet Name] )
            && 'Table'[Statement] = EARLIER ( 'Table'[Statement] )
            && 'Table'[Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
            && 'Table'[Date].[QuarterNo] = EARLIER ( 'Table'[Date].[QuarterNo] )
    ),
    'Table'[Index],
    ,
    ASC
)

 Result:

You can build a measure to get the 'Distinct' value by get the Min Rank in the values which has the same OuterName, Statement ,Year and Qtr.

Measure = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Rank]=MIN('Table'[Rank])))

Result:

The result in Red box is right.

If this reply still couldn't help you to solve your problem, please give me a sample table and show me the result you want.

If it is possible, you may tell me your calculated logic and what table will be used to build the measure.

Or you can provide me with your pbix file by your Onedrive for Business.

You can download the pbix file from this link: Filter in measures doesn't work while applying the measure to charts

Best Regards,

Rico Zhou

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