Forum Discussion

mberndt's avatar
mberndt
Regular Visitor
5 years ago

DAX help

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 Iterati...
Need Help
mberndt's avatar
mberndt
Regular Visitor
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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