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