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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
asharma7803
Frequent Visitor

MAX not working to display rows in Matrix

Hi,

 

I'm trying to display KPIs and KPIs result in a matrix for Max month selected in a date slicer. Requirement is to display KPIs result in a matrix for ONLY latest month from selected month range and display all selected months KPI results in a chart on the right.

 

Issue- Matrix is displaying data for all selected months instead of showing it only for latest month

 

Matrix screen - >

asharma7803_3-1665136379107.png

 

Measure - >

asharma7803_1-1665136254636.png

 

FIlter applied in Slicer -> 

asharma7803_2-1665136284494.png

 

Will greatly appreciate any help on this

 

1 ACCEPTED SOLUTION

Hi , @asharma7803 

I download your .pbix file, You want to show only the data for the maximum age of the slicer. Right?

Here are the steps you can refer to :

(1)You can add a column in your 'Date' table:

Date = 
ADDCOLUMNS (
    CALENDARAUTO(),    
    "Calendar Year", "CY" & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmm" ),
    "Month Number", MONTH ( [Date] ),
    "Year", YEAR( [Date] ),
    "Month Year", FORMAT ( [Date], "mmm" ) & "-" &YEAR ( [Date] ),
    "year_month", YEAR([Date])*100 + MONTH ( [Date] )
)

(2)We can create a measure like this:

Measure 2 = var _slice = MAXX(ALLSELECTED('Date'),[year_month])
var _current_year = SELECTEDVALUE( 'Date'[year_month])
return 
IF(_slice = _current_year ,1,0)

(3)Then we put the measure in the "Filter on this visual" and configure it:

vyueyunzhmsft_0-1665711729599.png

(4)Then we can meet your need , the result is as follows:

vyueyunzhmsft_1-1665711760443.png

Best Regards,

Aniya Zhang

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

15 REPLIES 15
v-yueyunzh-msft
Community Support
Community Support

HI , @asharma7803 

Based on your description, you want to show data for the largest month selected by the slicer. Right?

For custom date presentations, we can't associate our Date table with our fact table, you can use the 'Date' table as a slicer, then get the maximum year and month of the selection, and then use the IF function to judge whether the values in the Matri visual are displayed or not.

 

If this method does not meet your needs, you can provide us with your special sample data(table or .pbix file) and the desired output sample data in the form of tables, so that we can better help you solve the problem.

 

Best Regards,

Aniya Zhang

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

 

 

Hi @v-yueyunzh-msft,

 

Desired output is based on slicer in first page(Select Review and KPIs) If I select Aug 22 as last month data should appear only for only Aug 22 month as below in KPI Summary page

 

asharma7803_1-1665581686940.png

 

 

asharma7803_0-1665581655906.png

 

But data is appearing for last month as well for all previous months where data is available for KPIs.

 

asharma7803_2-1665581764478.png

 

I have tried if condition but that dint work as well. May be I am missing something.

Attaching PBIX file for your reference.

 

 

https://onedrive.live.com/?id=A9A36C9AEC54DDF7%21460&cid=A9A36C9AEC54DDF7 

 

Hi @v-yueyunzh-msft ,

 

One more thing to add, I have to also show  data for all selected months in the chart at right of table( in summary page) so applying Max filter in Filter pane will not also help as it will restrict data to last month in the chart.

Below is the chart in summary page Im talking about.

 

asharma7803_0-1665587084239.png

 

Sorry , below is the link that will work for PBIX file.

https://1drv.ms/u/s!AvfdVOyabKOpg0xriKvYOMajS3Wb?e=OWOlmH

Hi , @asharma7803 

You share the link, I can't download your .pbix file after opening it, can you share it with us through OneDrive?

vyueyunzhmsft_0-1665624803863.jpeg

 

Best Regards,

Aniya Zhang

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

 

Hi , @asharma7803 

I download your .pbix file, You want to show only the data for the maximum age of the slicer. Right?

Here are the steps you can refer to :

(1)You can add a column in your 'Date' table:

Date = 
ADDCOLUMNS (
    CALENDARAUTO(),    
    "Calendar Year", "CY" & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmm" ),
    "Month Number", MONTH ( [Date] ),
    "Year", YEAR( [Date] ),
    "Month Year", FORMAT ( [Date], "mmm" ) & "-" &YEAR ( [Date] ),
    "year_month", YEAR([Date])*100 + MONTH ( [Date] )
)

(2)We can create a measure like this:

Measure 2 = var _slice = MAXX(ALLSELECTED('Date'),[year_month])
var _current_year = SELECTEDVALUE( 'Date'[year_month])
return 
IF(_slice = _current_year ,1,0)

(3)Then we put the measure in the "Filter on this visual" and configure it:

vyueyunzhmsft_0-1665711729599.png

(4)Then we can meet your need , the result is as follows:

vyueyunzhmsft_1-1665711760443.png

Best Regards,

Aniya Zhang

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

 

 

Hi @v-yueyunzh-msft 

 

Hi @v-yueyunzh-msft 

 

I have found a solution and it is almost similar to your measure2 logic. But this measure helped me greatly to come up with the solution. Thank you so much:)

 

Regards,

Anil

tamerj1
Super User
Super User

Hi @asharma7803 

please try

Absolute Result =
CALCULATE (
    SUM ( VNKPI_results[ActualValue] ),
    'Date'[Date] = MAX ( 'Date'[Date] )
)

Hi @tamrej1

 

I tried it but getting error as below

asharma7803_0-1665145262897.png

 

@asharma7803 

Please try

Absolute Result =
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE ( SUM ( VNKPI_results[ActualValue] ), 'Date'[Date] = MaxDate )

or

Absolute Result =
VAR MaxDate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    CALCULATE ( SUM ( VNKPI_results[ActualValue] ), 'Date'[Date] = MaxDate )

Thank you @tamerj1, I have tried second solution but again returing previous months data. I have already tried your first solution in past and it doesnt wrk as well.

 

asharma7803_0-1665393466674.png

 

@asharma7803 
Ok, Then try

Absolute Result =
VAR MaxDate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ), ALL ( VNKPI_results ) )
RETURN
    CALCULATE ( SUM ( VNKPI_results[ActualValue] ), 'Date'[Date] = MaxDate )

Hi @tamrej1,

 

That dint work as well, data is returned for all months. I need data only for latest month that is August.

 

 

asharma7803_0-1665487007194.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors