topic Re: Divide two identical Measures which refer to filter in DAX Commands and Tips

Divide two identical Measures which refer to filter

Do5779 — Mon, 01 Aug 2022 11:21:11 GMT

Hi Experts,

I hope my question is at the right place of the forum.

Unfortunately I havn't found the right answer yet so I hope you can help me:

The main aim is to divide two measures. Thats clear, but it is a bit more complex.

I have two filters (date and a number) which filters each measure by the report user. The "number" is needed for the calculation. So for example Measure 1 (M1) is calculated at 01.01.2000 and the user choose the Number one, M2 for 03.02.2001 and Number 3.

Date-table for the example:

Date	Price
01.01.2000	2
03.02.2001	3

The calculation of each measure is absolutly identical to the other one. (For the example M1=Price * Number --> 2*1=2 / M2=3*3 =9)

Now I want to divide the result of M1 to M2 (M1/M2=>2/9=0,22). The calcualtion of M1 and M2 works (I displayed it at the board) but if I create a new measure called M3= M1/M2 it wont work. I have also no idea, how I can storage the subresult or write it with the dax formular calculate, because the filter are identical. Is there a possibility to refer direct to the filter? (In some other programming languages you can adress like Filter1.Date orr something like that)

Thanks a lot

Re: Divide two identical Measures which refer to filter

Barthel — Mon, 01 Aug 2022 15:23:44 GMT

Hey,

So the measure is based on the same table, only based on different parameters (date and number)? If you want to show the difference between the two, the parameters must come from separate tables, because one table cannot show two parameters at the same time.

So you have a total of four tables for your parameters:

Date parameter for M1 (Table: DateM1);
Date parameter for M2 (Table: DateM2);
Number parameter for M1 (Table: NumberM1);
Number parameter for M2 (Table: NumberM2).

Make sure the four tables are not connected through relationships in your model. These are the tables that your report users use as slicers. You can transfer the selected parameters as filter context using the CALCULATE function. This is what your code would look like for M1:

M1 = CALCULATE ( [Price] * [Number], TREATAS ( VALUES ( DateM1[Date] ), Table[Date] ), TREATAS ( VALUES ( NumberM1[Number] ), Table[Number] ) )

And so for M2:

M2 = CALCULATE ( [Price] * [Number], TREATAS ( VALUES ( DateM2[Date] ), Table[Date] ), TREATAS ( VALUES ( NumberM2[Number] ), Table[Number] ) )

And M3 is then the division:

M3 = DIVIDE ( [M1], [M2] )

Re: Divide two identical Measures which refer to filter

Do5779 — Tue, 02 Aug 2022 11:43:41 GMT

Tamks Barthel this solution works for now 😃 but is there a a way to pass a value from a slicer to my filter in only one direction so that there is no connecteion which kills my calculation? I want to set my slicer and the one filter is the start and the second the end of the period?

Slicer:

End of Period:

Beginn for the calculation:

Re: Divide two identical Measures which refer to filter

Barthel — Tue, 02 Aug 2022 14:07:12 GMT

Do5779 Sorry, but I don't quite understand what you mean. What is the differnce between your slicer and date texts? What tables are they based on and how are they related to each other?

Re: Divide two identical Measures which refer to filter

Do5779 — Wed, 03 Aug 2022 05:30:50 GMT

Barthel no problem, let me explain it a bit:

The slicer is for a duration and price diagram. At the beginning I calculate M1, which I have described at the beginning of my post. At the end date there is M2. Now the relative difference is calculated between the two dates, which is already working.

But the user have to set the filters for the calculation and then set the slicer to see the chart for the same duration (the start date of chart = M1 / End date of chart = M2).

What I originally wanted to do is that the user set the slicer of the bar chart and then there is the calculation of the measures, but how you explained there is no possibility because of the present connection.
Now the question is if "I can tell the filter for calculation M1" : If slicer date is 01.01.2000 then you have to be the 01.01.2000, without physically connection of the tables.

Re: Divide two identical Measures which refer to filter

Barthel — Thu, 04 Aug 2022 18:11:28 GMT

Hey Do5779

If I understand you correctly, you want to base both M1 and M2 on the slicer: M1 on the start date of the slicer and M2 on the end date. In this case you only need one date table. So you just need to use the 'DateM1' table from before and you could just call the table 'Date'. We can determine both the minimum and maximum of this table and apply this as filter context.

This is the code for M1:

M1 =
VAR MinDate =
    MIN ( Date[Date] )
RETURN
    CALCULATE (
        [Price] * [Number],
        Table[Date] = MinDate,
        TREATAS ( VALUES ( NumberM1[Number] ), Table[Number] )
    )

And this is the code for M2:

M2 =
VAR MaxDate =
    MAX ( Date[Date] )
RETURN
    CALCULATE (
        [Price] * [Number],
        Table[Date] = MaxDate,
        TREATAS ( VALUES ( NumberM1[Number] ), Table[Number] )
    )

Is this what you're looking for?

Re: Divide two identical Measures which refer to filter

Do5779 — Wed, 14 Sep 2022 11:35:24 GMT

Some time was, but I have to answer you, Barthel: Thank you very very much!
That was what I was looking for. And your explanation helped me a lot 👍