topic Re: Need help to write DAX or measure in DAX Commands and Tips

Need help to write DAX or measure

manojk_pbi — Thu, 14 Mar 2024 16:23:39 GMT

Hi Friends,

I am working on some data to represent the trends of % Change in Bugs. The change is always calculated for the current month & baseline will Janunary (opening counts). The baseline can be flexible sometime it can go to the previous years month. How can we write a DAX or measure where baseline month can be configure and % change can be calculated using the current data.

Please let me know if there are any suggesstions? It will be of great help.

For better understanding i am adding sample data and expected graph below

% Change formula = (Baseline Month Data - Current Month Data)/Baseline Month Data

Threshold Static - Expected % change

MonthYear	Product	Bugs	% Change	Threshold 100
Jan-24	Product 1	200
Mar-24	Product 1	190	5%	8%
Jan-24	Product 2	100
Mar-24	Product 2	125	-25%	8%
Jan-24	Product 3	130
Mar-24	Product 3	110	15%	8%
Jan-24	Product 4	160
Mar-24	Product 4	155	3%	8%
Jan-24	Product 5	20
Mar-24	Product 5	23	-15%	8%
Jan-24	Product 6	40
Mar-24	Product 6	38	5%	8%

Expected output from above table :

Thanks,

Re: Need help to write DAX or measure

Anonymous — Fri, 15 Mar 2024 08:04:39 GMT

Hi, manojk_pbi

Custom Column Index in PowerQuery

Then create new columns

Change = VAR _perviousIndex = CALCULATE ( MAX ( 'Table'[Index] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] < EARLIER ( 'Table'[Index] ) && 'Table'[Product] = EARLIER ( 'Table'[Product] ) ) ) VAR _perviousBugs = CALCULATE ( SELECTEDVALUE ( 'Table'[Bugs] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = _perviousIndex ) ) RETURN IF ( _perviousBugs <> BLANK (), ( _perviousBugs - 'Table'[Bugs] ) / _perviousBugs )

Threshold = IF ( [MonthYear] = DATE(2024,3,24), 0.08, BLANK() )

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

Re: Need help to write DAX or measure

manojk_pbi — Mon, 18 Mar 2024 06:55:48 GMT

Hi Anonymous ,

Thanks for you suggestion.

Since I am new to DAX queries, I couldn't understand the measure written to create a column. Is it assumed to be 2 values per product and then compared or can it work for N data points.

Please can you elaborate bit more on this works

Re: Need help to write DAX or measure

Anonymous — Mon, 18 Mar 2024 07:41:18 GMT

Hi, manojk_pbi

Select New A Column on the modeling page

Or right-click on your table to create a column instead of selecting a metric. These two are not quite the same

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

Re: Need help to write DAX or measure

manojk_pbi — Mon, 18 Mar 2024 07:44:47 GMT

This steps is clear. I didn't understand the logic of Change column how it works ? Is it fixed for 2 rows or can be used for N records

Re: Need help to write DAX or measure

Anonymous — Mon, 18 Mar 2024 08:33:45 GMT

Hi, manojk_pbi

It can be used for N records.

You can search for documentation on the relationship between Measure and Calculated column, such as the link below:

Calculated Columns and Measures in DAX - SQLBI

Best Regards

Yongkang Hua

Re: Need help to write DAX or measure

manojk_pbi — Mon, 18 Mar 2024 08:45:10 GMT

Thanks for your suggestions. I understood the logic now.

As per logic, always the difference is calculated with current & previous record. This is once of the scenario, actually it should calculate change w.r.t to Jan'24 or Start of the Year or even better if it can be configured.

Please could you help me with modified DAX for the same.

Thanks in advance.