Forum Discussion

Lily36876's avatar
Lily36876
Helper II
2 years ago

Use Parameter Values and Group by

Hi Guys,

 

I want to develop a report with setting parameters on filter,

and will directly effect on report result 

But now is fail when I want to group 【CalculateTransactionRange】this columns

 

I only want to show every shop's 【CalculateTransactionRange】and AMT

I try to use Summarize but seems can't get parameters in table...

 

Sample Data 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Lily36876 ,

     

    I can't download your attachment because of policy issues, can you describe your problem through a simple sample?

     

    Best regards,
    Community Support Team_ Scott Chang

    • Lily36876's avatar
      Lily36876
      Helper II
      2 years ago

      Dear Scott,

       

      Thanks for reply,

      My report is doing to show my shop's each sales price level ($1~$1000、$1001~$2000)

      I have four parameters, they work in my dax logic(it's no problem here)

      And then

      I only want to show each shop and each sales range, I don't need to show each sales record   

       

      May I know why i can't attach my .pbix file here?

      It's only can share via link?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Lily36876 ,

     

    I made simple samples and you can check the results below:

     

    Measure = VAR _t = ADDCOLUMNS('Table',"A",SUMX(FILTER(ALL('Table'),[ShopNo]=EARLIER([ShopNo])),[AMT]))
RETURN SUMX(_t,[A])

     

    Please feel free to correct me and provide more information if I have misunderstood you!

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Lily36876's avatar
      Lily36876
      Helper II
      2 years ago

      Hello!!!

       

      Thanks for reply, I think i missing to give this infomation.

      I don't have range column in data model,

      because range level is generated on what my parameter setting 

      and my range logic is below

       

      CalculateTransactionRange = 
VAR interval1 ='interval_1'[interval1 值]
VAR interval2='interval_2'[interval2 值]
VAR LOWERLimit='LOWER_Limit'[LOWERLimit 值]
VAR UPPERLimit='UPPER_Limit'[UPPERLimit 值]
VAR HPyTotal=SELECTEDVALUE(SalesRecord[HPyTotal])
VAR RANGE=
    SWITCH (
        TRUE (),
        HPyTotal < LOWERLimit,
        SWITCH (
            TRUE (),
            HPyTotal <= LOWERLimit,
            "0 ~ " & LOWERLimit,
            TRUE,
            INT((HPyTotal - LOWERLimit) / interval1) * interval1 + LOWERLimit + 1 & "~" & INT((HPyTotal - LOWERLimit)) / interval1 * interval1 + LOWERLimit + interval1
        ),
        HPyTotal >= LOWERLimit && HPyTotal <= UPPERLimit,
        SWITCH (
            TRUE (),
            HPyTotal <= LOWERLimit + interval1,
            LOWERLimit+1 & "~"  & LOWERLimit + interval1,
            HPyTotal = UPPERLimit,
            UPPERLimit - interval1 + 1 & "~"  & UPPERLimit,
            TRUE,
            INT((HPyTotal - LOWERLimit) / interval1) * interval1 + LOWERLimit + 1 & "~"  & INT((HPyTotal - LOWERLimit) / interval1) * interval1 + LOWERLimit + interval1
        ),
        HPyTotal > UPPERLimit+1,
        SWITCH (
            TRUE (),
            HPyTotal <= UPPERLimit + interval2,
            UPPERLimit + 1 & "~"  & UPPERLimit + interval2,
            TRUE,
            INT((HPyTotal - UPPERLimit - 1) / interval2) * interval2 + UPPERLimit + 1 & "~" & INT((HPyTotal - UPPERLimit - 1) / interval2) * interval2 + UPPERLimit + interval2
        ),
        "Other"
    )
RETURN RANGE