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
Mous007
Helper IV
Helper IV

Dynamic measure selection

Hi all,

 

I am trying to allow my users to compare overdues over the course of the month days.

 

I could easily achieve the above by creating seperate measures for each month as shown in the screen shot below:

 

Mous007_0-1604862483670.png

 

The issue with my approach is that i will have to create a specific measure for each month and hence not efficient.

 

I would like to dynamically be able to get the above results based on a slicer selection with the different months available ( e.g. user only select August and september on the slicer and the line graph populates automatically)

 

My measure is fairly simple = 

August overdue = CALCULATE(SUM(Consolidated[Overdue]) , 'Dates tables'[MonthName] = "August")
 
It would be great if  anyone can help me get my monthly measure dynamic using the slicer selection or guide me on how i should/can do it.
 
Thank you in advance
1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

Hi, @Mous007 , no worries, I come to your rescue, with the LEGEND of line graph 😂

Untitled.png

 

I presume your data is more or less like this,

2.png

The measure can be simplified to

Total Overdue = SUM( Consolidated[Overdue] )

 

You might want to refer to the attached file for more details. 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
Mous007
Helper IV
Helper IV

Thank you all for your replies.

 

@CNENFRNL your solution is actually what i am looking for for a first draft and it does the job indeed. I have no idea why i haven/t taught about it as it is fairly simple.

 

Thank you all again for the support

CNENFRNL
Community Champion
Community Champion

Hi, @Mous007 , no worries, I come to your rescue, with the LEGEND of line graph 😂

Untitled.png

 

I presume your data is more or less like this,

2.png

The measure can be simplified to

Total Overdue = SUM( Consolidated[Overdue] )

 

You might want to refer to the attached file for more details. 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Mous007
Helper IV
Helper IV

Hi @MattAllington , thank you for the prompt reply.

 

I am actually not trying to get the current month compared to previous month. But i would like to users to choose any month from the slicer (Jan to Dec) e.g. select July. Sep and Nov and show the corresponding values into the graph.

 

Apologies if my request isnt clear enough.

@Mous007 , In such a case you have two ways. Keep a snapshot of every month. Or rebuild based on logic when measure is called(Slicer values /date can help)

 

Dax append can help

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

 

Or you need to provide some logic to calculate. Can you share sample data and sample output in table format?

You want to write 2 measure. OD this month and OD last month. There are different ways to do this. I personally prefer adding a MonthID column to your calendar, eg 1 for the first month of the calendar, 2 for the second month 13 for the 13th month, 24 for the 24th month, etc. This allows you to easily identify the current and prior month. 


then write

 

TM overdue = CALCULATE(SUM(Consolidated[Overdue]) , filter(all('Dates tables'),Dates tables'[MonthName] = max('Dates tables'[MonthID])))

 

PM overdue = CALCULATE(SUM(Consolidated[Overdue]) , filter(all('Dates tables'),Dates tables'[MonthName] = max('Dates tables'[MonthID])-1))


place slicers on your report to select the current month, and the meausres will update. You can't control the name of the measures, but you can use an additional measure to generate a title for the chart, such as "Overdue for August and Sept"


ps. It is probably easier to understand when written with variables. 
eg

PM overdue = VAR SelectedMonth = max('Dates tables'[MonthID])))

VAR PriorMonth = SelectedMonth - 1

RETURN CALCULATE(SUM(Consolidated[Overdue]) , filter(all('Dates tables'),Dates tables'[MonthName] = PriorMonth))

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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