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              ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

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
    8 years ago

    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