DAX help

Question

Hi guys,&nbsp;I would like to ask you to help me out with this problem.&nbsp;I have 2 tables, one is a fact table and the other one is dimension table. In the dimension table the only column is Iterations (whole number). In the fact table: TABLE_COUNT (size of tables) and iteration also. There's a relationship between the two tables.I would like to make a measure which calculates: TABLE_COUNT in each Iterations (so in Iteration (x)) divided by TABLE_COUNT in the first Iteration.Tablecount (x Iteration) / Tablecount (Min iteration)Then I would like to drag this measure into a visualization in which the x axis is Iteration.&nbsp;The measure I made:Measure =VAR&nbsp;iteration_min&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;'ITERATION'[ITERATION]&nbsp;)VAR&nbsp;min_tablecount&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'facttable'[TABLE_COUNT]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;ALL&nbsp;(&nbsp;ITERATION&nbsp;),&nbsp;iteracio_min&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;SUM&nbsp;(&nbsp;'facttable'[TABLE_COUNT]&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'facttable'[TABLE_COUNT]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;ALL&nbsp;(&nbsp;'ITERATION'[ITERATION]&nbsp;),&nbsp;iteration_min&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;Thank you in advance for your replies!

mberndt · Answer

Also if might help... If I change the measure to this:&nbsp;Measure =VAR&nbsp;iteration_min&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;'ITERATION'[ITERATION]&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;SUM&nbsp;(&nbsp;'NC_FALLOUT_TREND_DATA'[TABLE_COUNT]&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'NC_FALLOUT_TREND_DATA'[TABLE_COUNT]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;'ITERATION',&nbsp;'ITERATION'[ITERATION]&nbsp;=&nbsp;iteration_min&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;I get 1,00 for the first row (minimum Iteration) and then 0,00 for all the others.

greg_deckler · Answer

mberndt&nbsp;Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.

mberndt · Answer

Greg_Deckler&nbsp;Thank you for help &amp; reply!&nbsp;Sample data:ITERATION;TABLECOUNT1;51;62;72;63;53;4&nbsp;Expected result:ITERATION; MEASURE1;12;1,181818181818183;0,818181818181818&nbsp;So for example for Iteration 2 the calculation is:(7 + 6) / (6 + 5)&nbsp;In the numerator I would like to sum the table_count values in iteration1 always. (The MIN(ITERATION))&nbsp;Hope it's more clear now. Please let me know if you need anything else.

anonymous · Answer

Hi&nbsp;mberndt,
Maybe you can try to use the following measure formulas, it will compare the current count and previous count:
Measure = 
VAR _current =
    MAX ( Test[ITERATION] )
VAR _previous =
    CALCULATE (
        MAX ( Test[ITERATION] ),
        FILTER ( ALLSELECTED ( Test ), [ITERATION] &lt; _current )
    )
RETURN
    IF (
        _previous &lt;&gt; BLANK (),
        DIVIDE (
            SUM ( Test[TABLECOUNT] ),
            CALCULATE (
                SUM ( Test[TABLECOUNT] ),
                FILTER ( ALL ( Test ), [ITERATION] = _previous )
            ),
            0
        ),
        1
    )

BTW, I'm not so sure how the 0.81 calculated, can you please explain more about this result?Regards,
Xiaoxin SHeng

Forum Discussion

DAX help

4 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX