topic Re: DAX help to use the distinct values of a measure in another measure in DAX Commands and Tips

DAX help to use the distinct values of a measure in another measure

engel_baut — Mon, 01 Jul 2024 12:37:47 GMT

Hi everyone,

I'm working on a big dataset to produce triangles & affiliated figures (for an insurance report). I was able to make the claims triangle without too much trouble as well as the related coefficients. The data at the end looks like this:

MONTH	1	2	3	4	5	6
COEFFICIENT	2,4	2,3	1,6	1,3	1,3	1,2

And so on up to month 36.

The coefficient continues to decrease until it reaches 1 and does so depending on a selected value on a slicer (basically, it can go to 1 in 6, 12, 24 or 36 months). This dynamic behaviour is required. Month comes from a separate table.

The coefficient comes from a measure so what I've described above is a matrix view, not a table view.

Now comes the fun part, I need to calculate, for each month, the inverse of the product of each month's coefficient up to the 36th (so the first value is 1 / (2.4 * 2.3 * 1.6 * 1.3 * 1.3 * 1.2 * ...) , the second value is 1 / (2.3 * 1.6 * 1.3 * 1.3 * 1.2 * ...) and so on). The final data should be the following:

MONTH	1	2	3	4	5	6
COEFFICIENT	2,4	2,3	1,6	1,3	1,3	1,2
PRODUCT	17,911296	7,46304	3,2448	2,028	1,56	1,2
FINAL MEASURE (1/PRODUCT)	6%	13%	31%	49%	64%	83%

For the life of me, I cannot get this to work and I think it's because I can't get the values of the measure coefficient to behave nicely (aka, the way a column would work). I've tried using DISTINCT or VALUES on the measure, but they don't work on measures, I've tried to summarize and ADDCOLUMNS with the measure but no dice. It is crucial that the Coefficient remains a measure as I need to be able to filter things in the matrix view.

Does anyone have an idea on how to proceed? If more data is needed to grasp the situation, don't hesitate to ask. Thank you so much in advance.

EDIT: I'm adding a wetransfer link to an example I created directly on PowerBI so that it would be clearer; would really appreciate any help on this!

https://we.tl/t-TVDzBQ6oXi

Re: DAX help to use the distinct values of a measure in another measure

lbendlin — Tue, 18 Jun 2024 22:46:15 GMT

Before doing anything else - unpivot your source data. Wide tables are an Excel thing, not something you want to do in Power BI.

Re: DAX help to use the distinct values of a measure in another measure

engel_baut — Wed, 19 Jun 2024 12:52:37 GMT

Hi,

Thanks for your answer, I think I wasn't clear: the source data is unpivoted. The pivot table is only in the visual, & the measure I do for the coefficients is then calculated accordingly to the pivot layout. I don't think I could do it another way either, but if you have any ideas I'll gladly take them?

Thanks again

Re: DAX help to use the distinct values of a measure in another measure

lbendlin — Wed, 19 Jun 2024 14:31:21 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX help to use the distinct values of a measure in another measure

engel_baut — Thu, 20 Jun 2024 13:58:39 GMT

Hi,

Thanks for the help.

I made a mockup of the report to include the current measure I have.

Basically, the measure for the coefficient is related to the selected Period chosen by the user in the slicer.

I also added another slicer with an "AREA" field, as I have to include an AREA option in my actual report, so that the user can chose to pick what the coefficients look like for area A or area B if they want to.

For the expected result, since I didn't manage to create the measure, I ended up exporting the matrix for each selected "Period", and do it manually on Excel, so I'm including this file as well. I calculated the desired outcome for each possible selected case (Period = 6, Period = 12, etc.)

https://we.tl/t-JwpRCw3MCu

Please tell me if this sample is not enough/if something else is needed to explain myself better.