cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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]
Capture1.PNG
The chart only shows correct value when I add additional slicers:
Capture.PNG
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
1 ACCEPTED SOLUTION

Accepted Solutions

Hi @DearestYoki 

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

1.png

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:

2.png

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:

3.png

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. 

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Yoki

Hi @DearestYoki 

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

1.png

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:

2.png

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:

3.png

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. 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors