DAX help

[mberndt]

Hi guys,

I would like to ask you to help me out with this problem.

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. 

The measure I made:

Measure =
VAR iteration_min =
    MIN ( 'ITERATION'[ITERATION] )
VAR min_tablecount =
    CALCULATE (
        SUM ( 'facttable'[TABLE_COUNT] ),
        FILTER ( ALL ( ITERATION ), iteracio_min )
    )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( 'facttable'[TABLE_COUNT] ) ),
        CALCULATE (
            SUM ( 'facttable'[TABLE_COUNT] ),
            FILTER ( ALL ( 'ITERATION'[ITERATION] ), iteration_min )
        ),
        0
    )

Thank you in advance for your replies!

[mberndt]

Also if might help... If I change the measure to this:

Measure =
VAR iteration_min =
    MIN ( 'ITERATION'[ITERATION] )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( 'NC_FALLOUT_TREND_DATA'[TABLE_COUNT] ) ),
        CALCULATE (
            SUM ( 'NC_FALLOUT_TREND_DATA'[TABLE_COUNT] ),
            FILTER ( 'ITERATION', 'ITERATION'[ITERATION] = iteration_min )
        ),
        0
    )

I get 1,00 for the first row (minimum Iteration) and then 0,00 for all the others.

[Greg_Deckler]

@mberndt 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/1116882

Also, 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/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[mberndt]

@Greg_Deckler Thank you for help & reply!

Sample data:

ITERATION;TABLECOUNT
1;5
1;6
2;7
2;6
3;5
3;4

Expected result:

ITERATION; MEASURE

1;1

2;1,18181818181818

3;0,818181818181818

So for example for Iteration 2 the calculation is:

(7 + 6) / (6 + 5)

In the numerator I would like to sum the table_count values in iteration1 always. (The MIN(ITERATION))

Hope it's more clear now. Please let me know if you need anything else.

[Anonymous]

Hi @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] < _current )
    )
RETURN
    IF (
        _previous <> 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