Forum Discussion

rawmeat's avatar
rawmeat
Frequent Visitor
8 years ago
Solved

Dax dynamic grouping based on the parameter passed by users

Hi all,

 

I am trying to migrate tableau report to power bi report.

The table looks like this:

Category     IP       Diff     Date

Lay              A         5       4/22/2018

Lay              A         10      4/21/2018

Lay              B         10       4/21/2018

Sch              C         40       4/20/2018

Sch              B         20       4/21/2018

Sch              C         30       4/21/2018

Rtl               A          20       4/21/2018

In tableau we defined a calculated field called "IP Diff Count" using fixed: 

 

{FIXED [IP] : SUM([Diff])}

 

In our scenario, we have one low parameter and high parameter which allows users to pass the offset value of IP Diff Count and then we created this measure:

 

IF ATTR([Category]) == "lay" THEN
    IF ATTR([IP Diff Count]) >= [High] THEN
        "High (>=[High])"
    ELSEIF ATTR([IP Diff Count]) > [Low] AND ATTR([IP Diff Count]) < [High] THEN
        "Medium ([Low]<>[High])"
    ELSEIF ATTR([IP Diff Count]) > 0 AND ATTR([IP Diff Count]) <= [Low] THEN
        "Low (<=[Low])"
    ELSE
        NULL
    END
ELSEIF.....(for other two categories)

Baiscially it means if the measure IP Diff Count for that category is smaller then the parameter pass by the user it will count it as "Low" otherwise it will be "Medium" or "High"

 

The final visual on tableau report is to display CNTD(IP) (distinct count of IP for "Low", "Medium" and "High")

(I already create a calculated column "IP DIFF COUNT" Using)

IP DIFF COUNT = CALCULATE(SUM(Table1[Diff]),FILTER(Table1,Table1[IP]=EARLIER(Table1[IP])))

 

For example based on the table above, and let's say if the user pass parameter for low "25" and high "35" It should return something like 

                                  Date 

Category                   4/20/2018                            4/21/2018                                4/22/2018

                         Low        Medium     High          Low        Medium     High          Low        Medium     High

"rtl"                 

"Sch"

"Lay"              

 

THX

 

  • Hi

    I use your dataset and measures (modified to fit in Power BI), finally, I get this

    Does this meet your needs?

     

    My steps are as follow

    1.Create new parameters in Editor Query, after they are created, right-click on the parameter and make the “Enable Load” checked, so the parameter can be load to the data model and used in the measure.

    2.Create two measures:

    IP DIFF COUNT =
    CALCULATE (
        SUM ( Table1[Diff] ),
        FILTER ( Table1, Table1[IP] = SELECTEDVALUE ( Table1[IP] ) )
    )

     

    Measure2 =
    IF (
        MAX ( [Category] ) = "Lay",
        IF (
            [IP DIFF COUNT] >= MAX ( High[High] ),
            "High",
            IF (
                [IP DIFF COUNT] > MAX ( Low[Low] ),
                "Medium",
                IF (
                    [IP DIFF COUNT] > 0
                        && [IP DIFF COUNT] <= MAX ( Low[Low] ),
                    "Low",
                    BLANK ()
                )
            )
        ),
        IF (
            MAX ( [Category] ) = "Sch",
            IF (
                [IP DIFF COUNT] >= MAX ( High[High] ),
                "High",
                IF (
                    [IP DIFF COUNT] > MAX ( Low[Low] ),
                    "Medium",
                    IF (
                        [IP DIFF COUNT] > 0
                            && [IP DIFF COUNT] <= MAX ( Low[Low] ),
                        "Low",
                        BLANK ()
                    )
                )
            ),
            IF (
                MAX ( [Category] ) = "Rtl",
                IF (
                    [IP DIFF COUNT] >= MAX ( High[High] ),
                    "High",
                    IF (
                        [IP DIFF COUNT] > MAX ( Low[Low] ),
                        "Medium",
                        IF (
                            [IP DIFF COUNT] > 0
                                && [IP DIFF COUNT] <= MAX ( Low[Low] ),
                            "Low",
                            BLANK ()
                        )
                    )
                )
            )
        )
    )

    3.add columns to a matrix

     

     

    Best Regards

    Maggie

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support

    Hi

    I use your dataset and measures (modified to fit in Power BI), finally, I get this

    Does this meet your needs?

     

    My steps are as follow

    1.Create new parameters in Editor Query, after they are created, right-click on the parameter and make the “Enable Load” checked, so the parameter can be load to the data model and used in the measure.

    2.Create two measures:

    IP DIFF COUNT =
    CALCULATE (
        SUM ( Table1[Diff] ),
        FILTER ( Table1, Table1[IP] = SELECTEDVALUE ( Table1[IP] ) )
    )

     

    Measure2 =
    IF (
        MAX ( [Category] ) = "Lay",
        IF (
            [IP DIFF COUNT] >= MAX ( High[High] ),
            "High",
            IF (
                [IP DIFF COUNT] > MAX ( Low[Low] ),
                "Medium",
                IF (
                    [IP DIFF COUNT] > 0
                        && [IP DIFF COUNT] <= MAX ( Low[Low] ),
                    "Low",
                    BLANK ()
                )
            )
        ),
        IF (
            MAX ( [Category] ) = "Sch",
            IF (
                [IP DIFF COUNT] >= MAX ( High[High] ),
                "High",
                IF (
                    [IP DIFF COUNT] > MAX ( Low[Low] ),
                    "Medium",
                    IF (
                        [IP DIFF COUNT] > 0
                            && [IP DIFF COUNT] <= MAX ( Low[Low] ),
                        "Low",
                        BLANK ()
                    )
                )
            ),
            IF (
                MAX ( [Category] ) = "Rtl",
                IF (
                    [IP DIFF COUNT] >= MAX ( High[High] ),
                    "High",
                    IF (
                        [IP DIFF COUNT] > MAX ( Low[Low] ),
                        "Medium",
                        IF (
                            [IP DIFF COUNT] > 0
                                && [IP DIFF COUNT] <= MAX ( Low[Low] ),
                            "Low",
                            BLANK ()
                        )
                    )
                )
            )
        )
    )

    3.add columns to a matrix

     

     

    Best Regards

    Maggie

    • rawmeat's avatar
      rawmeat
      Frequent Visitor

      Thanks Maggie.

      Sorry for the incomplete information I gave.

      These are the expressions I write:

      I first use ealier to get IP DIFF COUNT.

      Then 

      Friday sch(High) = CALCULATE(DISTINCTCOUNT(Friday[IP]),FILTER(Friday,Friday[IP DIFF COUNT]>=[HighSch Value]  && Friday[CATEGORY]="sch"))
      
      Friday sch(low) = CALCULATE(DISTINCTCOUNT(Friday[IP]),FILTER(Friday,Friday[IP DIFF COUNT]>0&&Friday[IP DIFF COUNT]<=[LowSch Value] && Friday[CATEGORY]="sch"))
      
      Friday sch(Medium) = CALCULATE(DISTINCTCOUNT(Friday[IP]),FILTER(Friday,Friday[IP DIFF COUNT]>[LowSch Value]  && Friday[IP DIFF COUNT]<[HighSch Value] && Friday[CATEGORY]="sch"))

      So instead of using one expression I created three measures here. 

       

      Thanks again