How to display the groups that contains certain subgroup?

[PBI_newuser]

Hi,

How to display only the Part Number that have subgroup of suffix "-09" (highlight in yellow) but do not include those with only one subgroup of suffix "-09" (highlight in pink)?

[v-alq-msft]

Hi, @PBI_newuser 

Based on your description, I created data to reproduce your scenario, The pbix file is attached in the end.

Table:

You may create a measure as below.

Visual control = 
    IF(
        CALCULATE(
            DISTINCTCOUNT('Table'[SubGroup]),
            ALLEXCEPT('Table','Table'[PartNumber])
        )>1&&
        COUNTROWS(
            FILTER(
               ALLEXCEPT('Table','Table'[PartNumber]),
               RIGHT([SubGroup],2)="09"
            )
        )>0,
        1,0
    )

Then you may put the measure in the visual level filter to filter the result.

Best Regards

Allan

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

[PBI_newuser]

Hi @v-alq-msft , when i tried on larger dataset, it doesn't work. Please see the data here.

Is it possible to have a slicer "Type", if select "Type A", then those parts with suffix of "-09" show up, if select "Type B", then those parts with suffix of "-08" show up.

[v-alq-msft]

Hi, @PBI_newuser 

I can not access the data you provided. I modified data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test:

You may modify the measure like below.

Visual control = 
    IF(
        CALCULATE(
            DISTINCTCOUNT('Table'[SubGroup]),
            ALLEXCEPT('Table','Table'[PartNumber])
        )>1&&
        COUNTROWS(
            FILTER(
               ALLEXCEPT('Table','Table'[PartNumber]),
               RIGHT([SubGroup],2)="09"
            )
        )>0,
        IF(
            OR(
                "A" in DISTINCT(Test[Type])&&RIGHT(SELECTEDVALUE('Table'[SubGroup]),2)="09",
                "B" in DISTINCT(Test[Type])&&RIGHT(SELECTEDVALUE('Table'[SubGroup]),2)="08"
            ),
        1,0
        )
    )

Result:

Best Regards

Allan

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

[PBI_newuser]

Hi @v-alq-msft , I have updated the data URL here.

I would like to see the group of part numbers with suffix of "-09" when I select Type A.

For example, when I select Type A, the highlighted groups in the screenshot should show up.

[v-alq-msft]

Hi, @PBI_newuser 

You may create a new table like below. The pbix file is attached in the end.

Slicer:

Then you may modify the measures as below. When 'A' is selected, items ending with '09' display. When 'B' is selected, items with '07' display. When 'C' is selected, items with '10' display.

Visual control = 
SWITCH(
    SELECTEDVALUE(Slicer[Type]),
    "A",
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[PartNumber]=MAX('Table'[PartNumber])&&
                RIGHT([SubGroup],2)="09"
            )
        )>0,
        1,0
    ),
    "B",
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[PartNumber]=MAX('Table'[PartNumber])&&
                RIGHT([SubGroup],2)="07"
            )
        )>0,
        1,0
    ),
    "C",
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[PartNumber]=MAX('Table'[PartNumber])&&
                RIGHT([SubGroup],2)="10"
            )
        )>0,
        1,0
    ),
    1
)

Visual control2 = 
SWITCH(
    SELECTEDVALUE(Slicer[Type]),
    "A",
    IF(
        COUNTROWS(
            FILTER(
                ALL('data'),
                'data'[PartNumber]=MAX('data'[PartNumber])&&
                RIGHT([SubGroup],2)="09"
            )
        )>0,
        1,0
    ),
    "B",
    IF(
        COUNTROWS(
            FILTER(
                ALL('data'),
                'data'[PartNumber]=MAX('data'[PartNumber])&&
                RIGHT([SubGroup],2)="07"
            )
        )>0,
        1,0
    ),
    "C",
    IF(
        COUNTROWS(
            FILTER(
                ALL('data'),
                'data'[PartNumber]=MAX('data'[PartNumber])&&
                RIGHT([SubGroup],2)="10"
            )
        )>0,
        1,0
    ),
    1
)

Result:

Best Regards

Allan

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

[PBI_newuser]

Hi @v-alq-msft , it works if there is no filter is selected.

Is it possible to include this criteria in measure as user may select different slicers to view different data?

Filter of last 12 months:

No filter: 

[v-alq-msft · Accepted solution]

Hi, @PBI_newuser 

I am sorry for late reply. You may try replacing 'ALL' with 'ALLSELECTED' in measures like below.

Visual control2 = 
SWITCH(
    SELECTEDVALUE(Slicer[Type]),
    "A",
    IF(
        COUNTROWS(
            FILTER(
                ALLSELECTED('data'),
                'data'[PartNumber]=MAX('data'[PartNumber])&&
                RIGHT([SubGroup],2)="09"
            )
        )>0,
        1,0
    ),
    "B",
    IF(
        COUNTROWS(
            FILTER(
                ALLSELECTED('data'),
                'data'[PartNumber]=MAX('data'[PartNumber])&&
                RIGHT([SubGroup],2)="07"
            )
        )>0,
        1,0
    ),
    "C",
    IF(
        COUNTROWS(
            FILTER(
                ALLSELECTED('data'),
                'data'[PartNumber]=MAX('data'[PartNumber])&&
                RIGHT([SubGroup],2)="10"
            )
        )>0,
        1,0
    ),
    1
)

Best Regards

Allan

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

[PBI_newuser]

Hi @v-alq-msft , it works!!!! Thank you so much for all your help! Truly appreciate it! 😀

[amitchandak]

@PBI_newuser ,

Create  a measure

calculate([meausre], filter(Table, right(Table[part_number],2) ="09")

[PBI_newuser]

Hi @amitchandak , could you share how to do it?

Here's the Sample.