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

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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'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